Mashhura Jahan
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.
Download Practice Workbook
You can download the practice workbook from here.
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 |
Step-by-Step Procedures to Use Excel VBA to Translate Language with Formula
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.
Read More: How to Translate English to Hindi in Excel
Similar Readings
- How to Translate Chinese to English in Excel (2 Simple Methods)
- Translate Excel File from French to English (2 Quick Tricks)
- How to Translate Portuguese to English in Excel (4 Effective Ways)
- Translate Arabic to English in Excel (With Easy Steps)
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.
- 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.
Read More: How to Use Google Translate Formula in Excel (with Easy Steps)
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.
Read More: How to Translate Excel File to English (2 Easy Ways)
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.
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 formula in 3 easy steps. I hope this article was helpful to you. For more articles stay connected with ExcelDemy. Lastly, if you have any questions, feel free to let me know in the comment section below.
very nice & helpful.
Dear Mukibul Hasan,
Thanks for your appreciation. Stay in touch with ExcelDemy.
Regards
Shamima Sultana | Project Manager | ExcelDemy
Hello there,
thanks for the VBA code,
can you please guide me on error as “Name?” for4 Translate_test formula
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