VBA code for Microsoft Text Translator API

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

https://translatorbusiness.uservoice.com/knowledgebase/articles/1078534-microsoft-translator-on-azure
Action Required before April 30, 2017: Microsoft Translator Moves to Azure
How to sign up for Microsoft Translator on Azure

https://www.microsoft.com/cognitive-services/en-us/translator-api
The Free plan allows 2M characters per month.

Documentation and interactive trials :
http://docs.microsofttranslator.com/text-translate.html

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
https://hubtest.microsofttranslator-int.com/Help/Download/Microsoft%20Translator%20Hub%20User%20Guide.pdf
“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
https://sysmod.wordpress.com/2012/01/09/microsoft-translator-vba-code-sample/

Example:

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

Advertisements

About Patrick O'Beirne, spreadsheet auditor

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

11 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
    THX

  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).
    http://docs.microsofttranslator.com/text-translate.html

  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
    Alt+F11
    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.

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 )

Google+ photo

You are commenting using your Google+ 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