function to translate text in excel code example

Example: excel vba translate text

'Here is a VBA User Defined Function to translate text in an Excel cell. It 
'uitlizes Google Translate and thus requires an Internet connection:

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 will be: Chaque instant est un nouveau départ.


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

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


'And 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
'
'
'