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

Get FREE Advanced Excel Exercises with Solutions!

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.

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 Language in Excel Formula


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


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 Multiple Cells in Excel


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


Download Practice Workbook

You can download the practice workbook from here.


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.


Related Articles


<< Go Back to Translate in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mashhura Jahan
Mashhura Jahan

Mashhura Jahan, BSc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology, invested 1.5 years at ExcelDemy. In roles like Excel & VBA Content Developer, Excel Charts, and Dashboard course trainer, she wrote 90+ articles. She was previously part of the forum support team and a junior software analyst on the Excel Add-in project. With interests spanning Excel, VBA, Power Query, Python, Data Science, and Software Development, Mashhura brings a diverse skill set to her... Read Full Bio

4 Comments
  1. very nice & helpful.

    • Dear Mukibul Hasan,

      Thanks for your appreciation. Stay in touch with ExcelDemy.

      Regards
      Shamima Sultana | Project Manager | ExcelDemy

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo