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

 

Codes for Different Languages

Here’s a short list of language codes for some languages. You will find the detailed list on 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

Our sample dataset contains some text and their source and target language codes. We will translate these texts from the source language to the target language with a formula.

Dataset to Use Excel VBA to Translate Language with Formula


Step 1 – Create a User Defined Function in Excel

  • Go to the Developer tab from Ribbon.
  • Select Visual Basic.

Create User Defined Function in Excel

  • The Visual Basic Editor window will appear.
  • Select the Insert tab.
  • Select Module.

Opening Module to Use Excel VBA to Translate Language Formula

  • A Module will open.
  • Copy 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?

  • We created a function named translate_text and 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.
  • We used the div class to get the result or the translated text from HTML and declared it as constant.
  • We declared another constant named url_temp_src and set it as the Google Translate link.
  • We used the WorksheetFunction.EncodeURL method to make sure that the data will be URL encoded.
  • We used the Replace function to replace the source and target language code in the URL.
  • The WorksheetFunction.WebService method calls the web service using HTTP GET request and return response.
  • Next, the InStr function finds the position of the first occurrence of a string within another string.
  • We used an If Statement to check for a logical rest and give results accordingly.
  • The Mid function gets the translated text.
  • 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 2 – Use the Formula to Translate Language

  • Select the first cell where you want to get the translated text. We selected cell E6.
  • Insert the following formula.
=translate_text(B6,C6,D6)

Use Formula to Translate Language

  • Press Enter to get the result.

Here, in the translate_text function, we selected cell B6 as text_str, C6 as src_lang, and D6 as trgt_lang. The formula will translate the text in B6 from the language in cell C6 to D6.
  • Drag the Fill Handle down to copy the formula to the other cells.

  • We have copied the formula to the other cells and got the desired output.

Read More: How to Use Google Translate Formula in Excel


Step 3 – Check If Formula Is Returning Correct Values

  • Select the cell where you want to check the language. We selected cell E6.
  • Go to the Review tab.
  • Select Translate.

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

  • The Translator will appear on the right side of the screen.
  • Select the source language. We selected English because we translated the first cell from English.
  • 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

We have provided a practice sheet so you can practice how to use Excel VBA to translate language with formula.

Practice Sheet for Excel VBA to Translate Language with Formula


Download the Practice Workbook


Related Articles


<< Go Back to Translate in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
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

10 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

  3. Thank you very much for this information. I found this code after a long search. Greetings to you.

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

    • 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

  5. Thank you very much very helpful for me when I create XLM Tools in Excel for multipl countries in Asia.
    Not sure if you can help me on the below language that cannot work
    1. Simplified Chinese ?
    2. Traditional Chinese (zh)
    3. Central Khmer (km or khm)
    The rest works really well for Korean, Japanese, Thai, Indonesian….etc
    Thanks in advance if you can advise or help me with this.

    • Reply Lutfor Rahman Shimanto
      Lutfor Rahman Shimanto May 2, 2024 at 11:14 AM

      Hello Alan Ang

      Thanks for visiting our blog and asking an interesting question. You seek the language codes for Simplified Chinese, Traditional Chinese, and Central Khmer. Please check the following table:

      Language Language Code
      English en
      Simplified Chinese zh-CN
      Traditional Chinese zh-TW
      Central Khmer km

      You can easily translate and fulfil your goal using the above language codes and the user-defined function mentioned in this article.

      I hope you have found the language code you were looking for. I have attached the workbook used to investigate your question; good luck.

      DOWNLOAD WORKBOOK

      Regards
      Lutfor Rahman Shimanto
      ExcelDemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo