Hello experts, i modify actual script to create a function in Excel to translate from Google Translator with formula.
In order to work, you need to add Reference "Microsoft Internet Controls" (Tools>References)
I create it referring to a cell range value with syntax =GoogleTranslate(A1,”en”,”es”)
Is there a way to use, as well, Cell Range and/or Quoted Text? I mean, both with same formula function. I mean, use any of these two ways of syntax:
=GoogleTranslate(A1,”en”,”es”)
OR
=GoogleTranslate(“Specific Text in Quotes”,”en”,”es”)
Here is the VBA code for this function (Add as Module):
In order to work, you need to add Reference "Microsoft Internet Controls" (Tools>References)
I create it referring to a cell range value with syntax =GoogleTranslate(A1,”en”,”es”)
Is there a way to use, as well, Cell Range and/or Quoted Text? I mean, both with same formula function. I mean, use any of these two ways of syntax:
=GoogleTranslate(A1,”en”,”es”)
OR
=GoogleTranslate(“Specific Text in Quotes”,”en”,”es”)
Here is the VBA code for this function (Add as Module):
VBA Code:
'***Google Translation Custom Function START ***
'Google Language Codes List:
'Auto Detect = 0 Bulgarian = bg Finnish = fi Hungarian = hu Latvian = lv Russian = ru Turkish = tr
'English = en Catalan = ca French = fr Icelandic = is Lithuanian = lt Serbian = sr Ukrainian = uk
'Afrikaans = af Chinese = zh Galician = gl Indonesian = id Macedonian = mk Slovak = sk Urdu = ur
'Albanian = sq Croatian = hr Georgian = ka Irish = ga Malay = ms Slovenian = sl Vietnamese = vi
'Arabic = ar Czech = cs German = de Italian = it Maltese = mt Spanish = es Welsh = cy
'Armenian = hy Danish = da Greek = el Japanese = ja Norwegian = no Swahili = sw Yiddish = yi
'Azerbaijani = az Dutch = nl Gujarati = gu Kannada = kn Persian = fa Swedish = sv
'Basque = eu Esperanto = eo Haitian_Creole = ht Korean = ko Polish = pl Tamil = ta
'Belarusian = be Estonian = et Hebrew = iw Lao = lo Portuguese = pt-PT Telugu = te
'Bengali = bn Filipino = tl Hindi = hi Latin = la Romanian = ro Thai = th
Function ConvertToGet(val As String)
val = Replace(val, " ", "+")
val = Replace(val, vbNewLine, "+")
val = Replace(val, "(", "%28")
val = Replace(val, ")", "%29")
ConvertToGet = val
End Function
Function Clean(val As String)
val = Replace(val, """, """")
val = Replace(val, "%2C", ",")
val = Replace(val, "'", "'")
Clean = val
End Function
Public Function RegexExecute(str As String, reg As String, Optional matchIndex As Long, Optional subMatchIndex As Long) As String
Dim RegEx, match, Matches
On Error GoTo ErrorHandler
Set RegEx = CreateObject("VBScript.RegExp"): RegEx.Pattern = reg
RegEx.Global = Not (matchIndex = 0 And subMatchIndex = 0)
If RegEx.Test(str) Then
Set Matches = RegEx.Execute(str)
RegexExecute = Matches(matchIndex).SubMatches(subMatchIndex)
Exit Function
End If
ErrorHandler:
RegexExecute = CVErr(xlErrValue)
End Function
Public Function GoogleTranslate(rng As Range, Optional translateFrom As String = "en", Optional translateTo As String = "es")
Dim getParam As String, trans As String, objHTTP As Object, URL As String
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
getParam = ConvertToGet(rng.Value)
URL = "https://translate.google.pl/m?hl=" & translateFrom & "&sl=" & translateFrom & "&tl=" & translateTo & "&ie=UTF-8&prev=_m&q=" & getParam
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.Send ("")
If InStr(objHTTP.responseText, "div dir=""ltr""") > 0 Then
trans = RegexExecute(objHTTP.responseText, "div[^""]*?""ltr"".*?>(.+?)</div>")
GoogleTranslate = Clean(trans)
Else
GoogleTranslate = CVErr(xlErrValue)
End If
End Function
Sub RegisterGoogleTranslatorFunction()
Dim strFunc As String 'name of the function you want to register
Dim strDesc As String 'description of the function itself
Dim strArgs() As String 'description of function arguments
ReDim strArgs(1 To 3) 'The upper bound is the number of arguments in your function
strFunc = "GoogleTranslate"
strDesc = "Google Translator Custom function: " & vbNewLine & vbNewLine & _
"Formula Syntaxis =TRANSLATE(''A1'',''en'',''es'')" & vbNewLine & _
"To autodetect language use 0. Instead of ''en''" & vbNewLine & vbNewLine & _
"For additional languages visit: https://cloud.google.com/translate/docs/languages/"
strArgs(1) = "Select Cell value to Translate"
strArgs(2) = "Tranlate FROM Language"
strArgs(3) = "Tranlate TO Language"
Application.MacroOptions Macro:=strFunc, Description:=strDesc, ArgumentDescriptions:=strArgs, Category:="Custom Category"
End Sub
'***Google Translation Custom Function FINISH ***
