VBA code for Microsoft Text Translator API

I have now updated the translator VBA source code to V3.


V2 was deprecated on April 30, 2018 . The Microsoft Translator Hub was to be retired on April 30, 2019. View important migration information and dates.

On Apr 30 Microsoft extended availability of version 2 of the Translator Text API:
“After receiving strong customer feedback about the scheduled retirement of version 2, we’ve decided to continue availability of this version along with version 3. Your applications that use version 2 will remain functional. Although version 2 will continue to be available, please migrate your applications to version 3 as your development cycle allows. By moving to version 3, you get access to exclusive functions, such as neural models, which are trained through Custom Translator.”

The V2 content below is now obsolete. See the V3 link above for new content.

Microsoft retired the Datamarket Translation API on April 30, 2017.
You need to sign up to the Azure Portal and use the Text Translator API in Cognitive Services.

Action Required before April 30, 2017: Microsoft Translator Moves to Azure
How to sign up for Microsoft Translator on Azure

The Free plan allows 2M characters per month.

Documentation and interactive trials :

You define an Account Name and get two keys. Either work, I don’t know yet what the difference is.

Before using “Try out a Method” on that page, you first use your Key 1 to get an access token that lasts 10 minutes and then you can try the methods.
In the methods, leave the Authorization blank, and for appid specify Bearer followed by a space and the access token.

To use your key in VBA, I now provide a VBA module that I use in Excel for translation.
You may use this free with attribution. I am available for custom Excel/VBA development work.

Download here: http://www.sysmod.com/MSTranslator4.bas

A test with 9,900 phrases in 23 European languages totalling 241K took under 30 minutes to complete.
I did not use Sleep to throttle the performance, and no translations were missed.
Microsoft do not indicate a rate limit, only the Azure Subscription Plan total: 2M characters/month for the free plan.
To learn more about the technology, see
“This guide will benefit any person who is interested in building a custom translation system using the Hub. A deeper background in machine translation is not essential to use the Hub. “

My old post from 2012 on the Datamarket translator is at


en Translate
pt Traduzir
fr Traduire
de Übersetzen
fi Kääntää
nl Vertalen
es Traducir
it Traduci
no Oversette
ja 翻訳
zh-CHS 翻译
ar ترجمة

About Patrick O'Beirne, Excel/VBA developer

Patrick provides consultancy and training in spreadsheet automation, data analysis, testing and model review; and the Excel spreadsheet auditing addin XLtest
This entry was posted in Excel/VBA, Open Source, Productivity and tagged , . Bookmark the permalink.

21 Responses to VBA code for Microsoft Text Translator API

  1. Pingback: Microsoft Translator VBA code sample | Patrick O'Beirne @ sysmod

  2. Fahad says:

    good day gentleman
    I have Err
    webRequest.send (sRequest) ‘ brackets to coerce Variant type if using late binding

  3. Fahad, if you said what the Err was, and give an example of the webrequest, I might be able to help.

  4. Fahad says:

    Yes Sir
    Run-Time error ‘-2146697211(800c0005)’:
    the system cann locate the resource specified

  5. So, your web request is looking for a resource that is not there. I looked up the MS page I linked above with “try out a method” but they don’t have that facility any more. All I can suggest is that you check that you have followed all the steps above to create an Azure cognitive services account and have the correct Account Key (Key 1 or Key 2).

  6. IDEA says:

    Hi Patrick. Total VBA virgin here. I need some help setting this up. It’s great that you’ve shared your code. I’ve downloaded your module and added in the key… but how do I actually run it from a cell and what parameters do I add? e.g. =Microsofttranslate(A1,En,Fr) ??? Thanks for your help.

    • You can do . =Microsofttranslate(A1,”En”,”Fr”) but as another contributor pointed out those UDFs are evaluated with every change which may impact performance. Try it by all means but you’ll probably want to learn how to call it from VBA for performance reasons.

  7. IDEA says:

    OK something’s up then because I don’t get any of the functions when I type into a cell… I’ve got it saved as a “module” I can see all the functions under the declarations pull-down menu. When I hit F5 though – I get an “open macro” dialogue. Like I said – virgin…

  8. The function names don’t appear when typing a formula because the code begins with “Option Private Module”. If you comment that out by putting an apostrophe before it, they will appear.
    If you type “virgin” into A1 and in B1 type =Microsofttranslate(A1,”En”,”Fr”)
    what do you get?

    From scratch:
    New workbook
    File > Import File > MSTranslator4.bas
    Open Module1
    Look for
    Const ACCOUNT_KEY As String = “Your Account Key”
    Change that to your MS Azure API key eg “abc123blahblah…”

    In a worksheet cell A1 enter “translate”
    in B1 enter =microsofttranslate(A1,”en”,”fr”)

    To test it in VBA, enter some French text in a cell
    In the VBA Editor, place the cursor inside Sub testMicrosoftTranslate()
    and press F5.

  9. JT says:

    Hello Patrick ! Sorry to update this subject, but I think it could be important. Azure translation will move to V3 end of April and it appears XML will no longer be working as the API will only use Json. Do you have any idea if this will impact your amazing macro ? https://docs.microsoft.com/en-us/azure/cognitive-services/translator/migrate-to-v3

  10. It’ll be a complete rewrite for JSON. I hope to have it this week

  11. barthulley says:

    Hi Patrick – I’ve been using your vba for my PhD research. An update for V3 would be awesome!

  12. Deepak says:

    You are awesome Patrick. Love your work and creativity. I have one question how can I use Google API instead of Microsoft Azure. As Google translation is better than Microsoft.

  13. I’m referring to unidirectional Spanish-to-English translations here. Some of the translations pairs are not correct/appropriate, and this applies to both Google and Microsoft translation engines. In this respect, limiting input to simple syntax (subject-verb-object if possible) improves the situation somewhat. Nevertheless, there remain irreducible meaning/usage differences between Spanish/Spanish and South American/Spanish, which can be compounded by the English/English and USA/English ones. Is there any way to have a manual correction override using a locally-stored dictionary?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s