Translate text using vba

Question

Probably could be a rare petition, but here is the issue.

I am adapting an excel of a third-party to my organization. The excel is developed in English and the people of my organization just speaks Spanish. I want to use exactly the same code that the original worksheet have, I prefer don't touch it (although I can do it), so I want to use a function that every time that a msgbox appears (with the text in English), I translate the msgbox messages but without touching the original script. I am looking for a mask that could be called every time that a msgbox is invoked in the original code.

I prefer don't touch the original code because the third-party developer could change it frequently, and it could be very annoying to change the code every time that they do any little change.

Is that possible?

Answer 1

Here is a more streamlined way to use Excel VBA and Google... to translate text.

This VBA User Defined Function should be entered into a standard code module.

Function Translate$(sText$, FromLang$, ToLang$)
    Dim p1&, p2&, url$, resp$
    Const DIV_RESULT$ = "<div class=""result-container"">"
    Const URL_TEMPLATE$ = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
    url = URL_TEMPLATE & WorksheetFunction.EncodeURL(sText)
    url = Replace(url, "[to]", ToLang)
    url = Replace(url, "[from]", FromLang)
    resp = WorksheetFunction.WebService(url)
    p1 = InStr(resp, DIV_RESULT)
    If p1 Then
        p1 = p1 + Len(DIV_RESULT)
        p2 = InStr(p1, resp, "</div>")
        Translate = Mid$(resp, p1, p2 - p1)
    End If
End Function

With the following text in cell A1: Every moment is a fresh beginning.

In cell B1 enter this formula:

=Translate(A1, "en", "fr")    '<--translates text in A1 from English to French.

The result in cell B1: Chaque instant est un nouveau départ.

Of course this Translate() function can be used directly from VBA as well:

MsgBox Translate([A1], "en", "de")  '<--displays: Jeder Moment ist ein Neuanfang.

Of course you may also manually use the Translate functionality built into Excel, which can be found on the Review tab of the Ribbon. But the UDF above provides a quick and streamlined method to translate text programmatically. Excel's translation capability is not exposed via the Excel Object Model, so a function like the above can be quite useful.

The FromLang and ToLang arguments must be codes from the following table:

 CODE   LANGUAGE
 en     English
 fr     French
 es     Spanish
 it     Italian
 de     German
 af     Afrikaans
 sq     Albanian
 am     Amharic
 ar     Arabic
 hy     Armenian
 az     Azerbaijani
 eu     Basque
 be     Belarusian
 bn     Bengali
 bs     Bosnian
 bg     Bulgarian
 ca     Catalan
 ceb    Cebuano
 ny     Chichewa
 zh-CN  Chinese (Simplified)
 zh-TW  Chinese (Traditional)
 co     Corsican
 hr     Croatian
 cs     Czech
 da     Danish
 nl     Dutch
 eo     Esperanto
 et     Estonian
 tl     Filipino
 fi     Finnish
 fy     Frisian
 gl     Galician
 ka     Georgian
 el     Greek
 gu     Gujarati
 ht     Haitian Creole
 ha     Hausa
 haw    Hawaiian
 iw     Hebrew
 hi     Hindi
 hmn    Hmong
 hu     Hungarian
 is     Icelandic
 ig     Igbo
 id     Indonesian
 ga     Irish
 ja     Japanese
 jw     Javanese
 kn     Kannada
 kk     Kazakh
 km     Khmer
 rw     Kinyarwanda
 ko     Korean
 ku     Kurdish (Kurmanji)
 ky     Kyrgyz
 lo     Lao
 la     Latin
 lv     Latvian
 lt     Lithuanian
 lb     Luxembourgish
 mk     Macedonian
 mg     Malagasy
 ms     Malay
 ml     Malayalam
 mt     Maltese
 mi     Maori
 mr     Marathi
 mn     Mongolian
 my     Myanmar (Burmese)
 ne     Nepali
 no     Norwegian
 or     Odia (Oriya)
 ps     Pashto
 fa     Persian
 pl     Polish
 pt     Portuguese
 pa     Punjabi
 ro     Romanian
 ru     Russian
 sm     Samoan
 gd     Scots Gaelic
 sr     Serbian
 st     Sesotho
 sn     Shona
 sd     Sindhi
 si     Sinhala
 sk     Slovak
 sl     Slovenian
 so     Somali
 su     Sundanese
 sw     Swahili
 sv     Swedish
 tg     Tajik
 ta     Tamil
 tt     Tatar
 te     Telugu
 th     Thai
 tr     Turkish
 tk     Turkmen
 uk     Ukrainian
 ur     Urdu
 ug     Uyghur
 uz     Uzbek
 vi     Vietnamese
 cy     Welsh
 xh     Xhosa
 yi     Yiddish
 yo     Yoruba
 zu     Zulu