Translate text using vba

calendar_today Asked Sep 30, 2013
thumb_up 11 upvotes
history Updated April 16, 2026

Question posted 2013 · +3 upvotes

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 (althought 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 everytime 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 everytime that they do any little change.

Is that possible?

Accepted answer +11 upvotes

Here you go.

  Sub test()
    Dim s As String
    s = "hello world"
    MsgBox transalte_using_vba(s)

End Sub



 Function transalte_using_vba(str) As String
' Tools Refrence Select Microsoft internet Control


    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")
    '   TO CHOOSE INPUT LANGUAGE

    inputstring = "auto"

    '   TO CHOOSE OUTPUT LANGUAGE

    outputstring = "es"

    text_to_convert = str

    'open website

    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
    transalte_using_vba = result_data


End Function

2 code variants in this answer

  • Variant 1 — 6 lines, starts with Sub test()
  • Variant 2 — 45 lines, starts with Function transalte_using_vba(str) As String

Excel VBA objects referenced (4)

Top excel-vba Q&A (6)

+11 upvotes ranks this answer #55 out of 136 excel-vba solutions on this site .
excel-vba