Excel VBA to Translate Language with Formula (With Easy Steps)

If you are looking for a way to translate language with formula in VBA then you have come to the right place. Excel doesn’t provide a function for translation. But, you can easily make a user-defined function in VBA. The objective of this article is to explain how to use Excel VBA to translate language with a formula.

Codes for Different Languages

Before starting the procedure, you must know the language codes of different languages as you will have to provide language codes in the formula to translate from one language to another. For your convenience, I am providing the lists of some language codes here. You will find the detailed list in the Google Cloud web link.

Language Name	Language Code
Afrikaans	af
Arabic	ar
Bengali	bn
Chinese (Simplified)	zh-CN or zh
Croatian	hr
Danish	da
English	en
French	fr
German	de
Hindi	hi
Italian	it
Japanese	ja
Korean	ko
Latin	la
Polish	pl
Portuguese	pt
Russian	ru
Spanish	es
Thai	th
Urdu	ur

Excel VBA to Translate Language with Formula: Step-by-Step Procedures

I have taken the following dataset to explain this article. This dataset contains some text and their source and target language codes. I will translate these texts from the source language to the target language with a formula using Excel VBA. Let’s explore the step-by-step procedures.

Step-01: Create User Defined Function in Excel

Excel does not provide any function for translation. So, I will make a user-defined function in Excel VBA in this step.

Firstly, go to the Developer tab from Ribbon.
Secondly, select Visual Basic.

Thirdly, the Visual Basic Editor window will appear.
Select the Insert tab.
Then, select Module.

After that, a Module will open.
Next, write the following code in that Module.

Function translate_text$(text_str$, src_lang$, trgt_lang$)
Dim s1&, s2&, url_str$, rsp$
Const rslt_div$ = "<div class=""result-container"">"
Const url_temp_src$ = "https://translate.google.com/m?hl=[from]&sl=[from]&tl=[to]&ie=UTF-8&prev=_m&q="
url_str = url_temp_src & WorksheetFunction.EncodeURL(text_str)
url_str = Replace(url_str, "[to]", trgt_lang)
url_str = Replace(url_str, "[from]", src_lang)
rsp = WorksheetFunction.WebService(url_str)
s1 = InStr(rsp, rslt_div)
If s1 Then
s1 = s1 + Len(rslt_div)
s2 = InStr(s1, rsp, "</div>")
translate_text = Mid$(rsp, s1, s2 - s1)
End If
End Function

🔎 How Does the Code Work?

In the beginning, I created a function named translate_text. And, I declared the arguments for the function. The $ sign means as String.
Here, text_str is the text you want to translate, scr_lang refers to the language from which you want to translate, and trgt_lang refers to the language to which you want to translate.
Then, I declared some variables for the code.
After that, I used the div class to get the result or the translated text from HTML. And, declared it as constant.
Next, I declared another constant named url_temp_src and set it as Google Translate link.
Then, I used WorksheetFunction.EncodeURL method to make sure that the data will be URL encoded.
Afterward, I used the Replace function to replace the source and target language code in the URL.
Next, I used WorksheetFunction.WebService method to call the web service using HTTP GET request and return response.
Then, I used the InStr function to find the position of the first occurrence of a string within another string.
After that, I used an If Statement to check for a logical rest and give results accordingly.
Then, I used the Mid function to finally get the translated Text.
Next, I ended the If Statement.
Finally, I ended the function.

Then, Save the code and go back to your worksheet.

Step-02: Use Formula to Translate Language

In this step, I will use the user-defined function that I made in the previous step. I will write a formula to translate the language of the given text.

In the beginning, select the cell where you want to get the translated text. Here, I selected cell E6.
Then, in cell E6 write the following formula.

=translate_text(B6,C6,D6)

After that, press Enter to get the result.

Here, in the translate_text function, I selected cell B6 as text_str, C6 as src_lang, and D6 as trgt_lang. Now, the formula will translate the text in B6 from the language in cell C6 to D6.

Next, drag the Fill Handle down to copy the formula to the other cells.

Finally, you can see that I have copied the formula to the other cells and got the desired output.

Step-03: Check If Formula Is Returning Correct Value

This one is an optional step. Here, I will show you how you can check if the translation you get from the formula is correct. I will use the Translate feature from Excel to do that.

First, select the cell where you want to check the language. Here, I selected cell E6.
Next, go to the Review tab.
After that, select Translate.

Next, the Translator will appear on the right side of the screen.
Select it as the source language. Here, I selected English because I translated it from English.
In the following image, you can see that the translation is giving the correct value.

Things to Remember

For this formula to work, you must have your internet connection on. Otherwise, it will return a #VALUE! error.
Whenever working with VBA, you must save your Excel file as Excel Macro-Enabled Workbook. Otherwise, the VBA code will not work.

Practice Section

Here, I have provided a practice sheet for you so that you can practice how to use Excel VBA to translate language with formula.

Download Practice Workbook

You can download the practice workbook from here.

Creating Translate Function.xlsm

Conclusion

So, you have reached the end of my article. Here, I tried to explain how you can use Excel VBA to translate language with formulas in 3 easy steps. I hope this article was helpful to you. Lastly, if you have any questions, feel free to let me know in the comment section below.

<< Go Back to Translate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

8 Comments

Mukibul Hasan Mar 3, 2023 at 8:53 AM

very nice & helpful.
- Reply
  Shamima Sultana Mar 5, 2023 at 11:48 AM
  
  Dear Mukibul Hasan,
  
  Thanks for your appreciation. Stay in touch with ExcelDemy.
  
  Regards
  Shamima Sultana | Project Manager | ExcelDemy
Husain Dini Mar 7, 2023 at 2:28 PM

Hello there,
thanks for the VBA code,

can you please guide me on error as “Name?” for4 Translate_test formula
- Reply
  Rabeya Islam Mar 9, 2023 at 1:09 PM
  
  Dear HUSAIN DINI,
  
  Thanks for sharing your concern with us.
  
  The problem you are facing may occur if you misspell the name of the function in the worksheet cell.
  
  Another reason can be the use of incorrect syntax. Make sure to use all the arguments for the function in the correct order and separate them by a comma.
  
  For better understanding, you can share your Excel file with us in Exceldemy Forum, then we can find the specific problem in your file and solve that.
  
  Don’t forget to check our other articles for your Excel guidance.
  
  Regards,
  Rabeya Islam | Excel & VBA Content Developer | ExcelDemy
ابو معاذ Mar 3, 2024 at 10:12 PM

Thank you very much for this information. I found this code after a long search. Greetings to you.
- Reply
  Shamima Sultana Mar 7, 2024 at 9:08 AM
  
  Hello ابو معاذ,
  
  You are most welcome.
  
  Regards
  ExcelDemy
NILANJAN Apr 11, 2024 at 8:45 PM

Hello Mashhura Jahan.. this is a simple and but effective way for Excel on Windows , however I am having trouble using this with Excel on Macbook. could you please help me.
- Reply
  Shamima Sultana Apr 16, 2024 at 2:46 PM
  Hello Nilanjan,
  
  Excel for Mac doesn’t support the WorksheetFunction.WebService method due to differences in how Excel for Mac interacts with web services compared to Excel for Windows. In Mac, you need to directly access Google Translate’s API using HTTP requests or using a different method provided by Google Translate.
```
Function TranslateText(text_str As String, src_lang As String, trgt_lang As String) As String
    Dim url As String
    Dim xmlhttp As Object
    Dim response As String
    Dim json As Object
    
    ' Construct the URL for Google Translate API
    url = "https://translation.googleapis.com/language/translate/v2?key=YOUR_API_KEY&q=" & _
            VBA.URLEncode(text_str) & "&source=" & src_lang & "&target=" & trgt_lang

    ' Send HTTP request to Google Translate API
    Set xmlhttp = CreateObject("MSXML2.XMLHTTP")
    xmlhttp.Open "GET", url, False
    xmlhttp.send
    
    ' Parse the JSON response
    response = xmlhttp.responseText
    Set json = JsonConverter.ParseJson(response)
    
    ' Extract and return the translated text
    TranslateText = json("data")("translations")(1)("translatedText")
End Function
```
  Replace “YOUR_API_KEY” with your actual Google Cloud Platform API key.
  Remember to enable the “Microsoft XML, v6.0” reference in the VBA editor (under Tools > References) to use CreateObject(“MSXML2.XMLHTTP”) for making HTTP requests.
  
  Regards
  ExcelDemy