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.


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.

Dataset to Use Excel VBA to Translate Language with Formula


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.

Create User Defined Function in Excel

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

Opening Module to Use Excel VBA to Translate Language Formula

  • 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

VBA Code for User Defined Function to Write Formula to translate Language in Excel

🔎 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.

Saving Code for Excel VBA Translate Language Formula

Read More: How to Translate English to Hindi in Excel


Similar Readings


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)

Use Formula to Translate Language

  • 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.

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.

Check If Formula Is Returning Correct Value for Translate Language in Excel VBA

  • 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.

Practice Sheet for 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.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo