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

After recently making an Excel Speller

https://dhexcel1.wordpress.com/2017/05/28/excel-short-and-sweet-tip-19-excel-can-spell-by-david-hager/

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

https://stackoverflow.com/users/2227085/santosh

in the following discussion.

https://stackoverflow.com/questions/19098260/translate-text-using-vba

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:

https://msdn.microsoft.com/en-us/library/cc233982.aspx

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

DoEvents

Loop

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

Do Until IE.ReadyState = 4

DoEvents

Loop

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), “>”))

Next

IE.Quit

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.

Translate1

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.

xlTranslateFunction

Advertisements

3 thoughts on “Using Excel UDF to Translate a Phrase From One Language to Another by David Hager

  1. Pingback: #Excel Super Links #56 – shared by David Hager | Excel For You

  2. Pingback: #Excel Super Links #57 – shared by David Hager | Excel For You

  3. Pingback: #Excel For You Blog Posts Having Downloadable Workbook Files by David Hager | Excel For You

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s