Tag Archives: speak

Using Excel UDF to Translate a Phrase From One Language to Another by David Hager

After recently making an Excel Speller


I decided to explore other applications that speak. The most logical choice was to make an Excel Translator. After searching the net, I found a good source for this.

The core VBA procedure came from Santosh


in the following discussion.


There are a number of procedures that perform the same operation. However, none of them had a source of language codes to see, making them difficult to use without an external reference. Luckily, Microsoft has published a comprehensive list at:


I used Power Query to get the table from this site, load it in a worksheet, then unlink it from the query since it is a static table. I then modified the code to allow information to be looked up from the table to be used as part of the procedure. The complete code is here, with the underlined portions add by me.

Function Translate(str As String, lCode As String)

Dim IE As Object, i As Long

Dim inputstring As String, outputstring As String, text_to_convert As String, result_data As String, CLEAN_DATA

Set IE = CreateObject(“InternetExplorer.application”)

inputstring = “auto”

outputstring = lCode

text_to_convert = str

LangToLookup = Application.WorksheetFunction.Index([LCodes!b2:b150], Application.WorksheetFunction.Match(lCode, [LCodes!A2:A150], 0))

IE.Visible = False

IE.navigate “http://translate.google.com/#” & inputstring & “/” & outputstring & “/” & text_to_convert

Do Until IE.ReadyState = 4



Application.Wait (Now + TimeValue(“0:00:5”))

Do Until IE.ReadyState = 4



CLEAN_DATA = Split(Application.WorksheetFunction.Substitute(IE.document.getElementById(“result_box”).innerHTML, “</SPAN>”, “”), “<“)


For j = LBound(CLEAN_DATA) To UBound(CLEAN_DATA)

result_data = result_data & Right(CLEAN_DATA(j), Len(CLEAN_DATA(j)) – InStr(CLEAN_DATA(j), “>”))



Translate = result_data

Application.Speech.Speak (“In ” & LangToLookup & “, ” & str & “means” & Translate)

End Function

The 2nd argument of the TRANSLATE function was added, and the appropriate codes for that argument can be viewed on the LCodes worksheet. That variable was used with outputstring = lCode.

The following line of code returns the language name from the table.

LangToLookup = Application.WorksheetFunction.Index([LCodes!B2:B150], Application.WorksheetFunction.Match(lCode, [LCodes!A2:A150], 0))

The last line of code reads the information out loud.

Application.Speech.Speak (“In ” & LangToLookup & “, ” & str & “means” & Translate)

The result of using this function on the worksheet is shown below.


Be aware that some of the codes will not return a spoken phrase because of character differences (such as Hebrew).

You can download the file here.



#Excel Short and Sweet Tip #19 (Invaluable Excel Speller) by David Hager

Do you know about those kids toys that spells out the name of an object and then says the word? Well, you can do the same thing in Excel. This article is about the use of Speech.Speak in VBA, and this process provides a good demonstration of its use. Place this event procedure in a Worksheet module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

textInCell = Target.Value

If textInCell = “” Then Exit Sub

For n = 1 To Len(textInCell)

If Asc(Mid(textInCell, n, 1)) > 64 And Asc(Mid(textInCell, n, 1)) < 91 Then

Application.Speech.Speak “Capital ” & Mid(textInCell, n, 1)


Application.Speech.Speak Mid(textInCell, n, 1)

End If


Application.Speech.Speak (“spells ” & textInCell)

End Sub

Then, select a cell on the worksheet and if it contains a word it will spell the word and then say it. In the example file, A1 contains the word “Elephant”. If that cell is selected, it will be spelled out starting with “capital E” followed by the rest of the letters and then says “spells Elephant”.

You can download the file here. It also contains a greeting upon opening the workbook.