MS Azure Cognitive Services V3 API Translator VBA

I have now updated my free open-source VBA code for the MS Translator API V3, which uses JSON data formats rather than the previous XML. There is no change in your subscription method, you can continue to use the same Azure Account Key.
To use this code:

1. Download the ZIP file (13K) and unzip it into a folder.
(Updated 9-May-19 ClsJSONLib.cls)

2. Read the README.TXT file.

3. Run the Sub testMicrosoftTranslate() to verify that the code works

Comments and questions welcome. If you would like some tailoring to your specific requirements, or indeed any Excel VBA development, contact me to discuss a specification and quotation.


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 and tagged . Bookmark the permalink.

27 Responses to MS Azure Cognitive Services V3 API Translator VBA

  1. Pingback: VBA code for Microsoft Text Translator API | Patrick O'Beirne @ sysmod

  2. yasserkhalil says:

    Hello. Thanks a lot for this gift.
    How can I get the Azure key?

  3. Arthur says:

    Hi Patrick,
    Thanks for a great tool!
    I’m having this error when I’m trying to run “testMicrosoftTranslate” macro (API key is set):
    Run-time error ’91’:
    Object variable or With block variable not set

    debug points to this location (Ln 277, Col 15)
    Set oDict1 = oJson(1) ‘ collection

    Would be greatful for your response!

    • Hello Arthur,
      This is where you need to use the debugger to find out what the problem is.
      If you set a Quick Watch on oJson and examine it in the Watches window, what is it? It should be a collection with one item, a Dictionary object.
      Look back farther. What is in sResponseText ? That tells you what the result was.
      IF that is an error message, I should add a test for that for completeness sake. That code line 277 assumed a successful detection.
      And you need to find out what the error was, and avoid it.

  4. Oh, that’s not what I expected. It looks like sResponseText is valid but the parser result is Nothing rather than a JSON object. Maybe a new gotcha in Excel 2019 – what’s your version and build number? my latest is Office 365 version 1903. I’ll need to trace that. I wonder, could you do that in your version of Excel and see why the ParseJson() fn returns Nothing? Sorry, it’s quite tedious to trace the ClsJsonLib.cls code because of the character by character parsing.

    • Arthur says:

      I’m using Excel 2019 version 1903 (build 11425.20244). I’d be glad to help but it’ll be hard for me since I’m not sure what I need to do from my side (sorry, I’m not experienced in VBA). If you don’t mind guiding me step by step to trace this error it would be wonderful.

      • Patrick OBeirne says:

        Just try this for me:Anywhere you see “on error resume next “, remove it. In the Class too.

  5. Microsoft have 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.”

  6. 2-May: I updated ClsJSONLib.cls to remove an unnecessary On Error Resume Next. This should make any errors visible.

    • Arthur says:

      Hello Patrick,

      I’ve got a new error after the update. When I’m trying to run testMicrosoftTranslate:
      Compile error – user-defined type not defined (Ln 271, Col 4).
      After adding Watch to MicrosoftTranslatorDetect I get: Value – , Type – Empty.

      By the way, I found 0 instances of On Error Resume Next in module and 2 in class. If it’s still relevant I can try to remove both of them and see what happens…

  7. Arthur, In the Excel VBA Editor, Tools > Options > General, please check “Break in Class module”. Then try again and see where the error happens. Thank you!
    There should be only ONE Resume Next, In Function multiArray(… at line numbered 644, line 366 of the ClsJSONLib.cls file.
    It should NOT be in Public Function parse(…

    • Arthur says:

      Hello, Patrick! I’ve checked the option and got the error in ClsJSONLib (Ln 183, Col 1). I’ve added Watch to parseNumber and got this:
      Value = 0 | Tupe = Double | Context = ClsJSONLib.parseNumber

      • Arthur says:

        Sorry, the error is Type mismatch (Run-time error ’13’). Pointing at CDb1(Value) gives = Type mismatch

  8. Thank you! What is the type and value (content) of the variable Value, please?
    And what is your Excel locale? US/UK/…?

    • Arthur says:

      Added Watch to the Value and got:
      Value = “1.0” | Type = String
      Mind quotation marks (they are in Excel too)
      On one machine I have US locale and on another I have RU locale. Tested on both and getting same results (run-time error ’13’)

      • If .ru uses comma as decimal separator then the fix is to change Cdbl( to Val(.
        In US it makes no sense to me that “1.0” would give err 13.
        Removing any confidential data could you post the whole of what is in sResponsetext, or email it to me?
        Thanks again!

    • Arthur says:

      Patrick, thank you! The comma trick worked! Seems like both machines were working on RU locale, my bad! Now, after I run testMicrosoftTranlate I get this messages:

      String: cy–>en
      Good Day

      Detect can identify languages that Microsoft translator cannot translate.
      The score is between zero and one.

      How can I get the contents of sResponseText? Do I need to add Watch? At what position?

  9. Thank you Arthur and my apologies for taking so long to realise what the problem was. I’ll update the code to use VAL().

    To record sResponseText I would either write it to a file or simply to a cell in a new sheet like this
    Worksheets.Add.Range(“A1”).value = sResponseText

    I’d put that after the line
    lReturn = MSAPIV3Request(“POST”, sRequest, sRequestBody, sResponseText)

    I don’t need it now, the locale was the issue. CDBL() uses the Windows Regional Settings so in many European locales it expects a comma as a decimal separator, and the dot in 1.0 causes the error. VAL() uses the VBA settings which is always a dot (period).

    Required background reading for anyone internationalising VBA is the free Chapter 22 of the Excel 2002 VBA Programmer’s Reference
    Written by John Green, Stephen Bullen, Rob Bovey and Robert Rosenberg

    • Arthur says:

      Thank you Patrick! I’ve acquired results from sResponseText. It’s a long string of languages and their names, here is the example:

      “zh-Hans”:{“name”:”Китайский упрощенный”,”nativeName”:”简体中文”,”dir”:”ltr”}

      Notice that the name of the language is in my locale (RU). I guess that’s all I have to say about that 🙂

      • Yes, that’s the output of Sub testGetLanguagesForTranslate()

        Sub testMicrosoftTranslatorDetect() returns JSON like this, where the score is the number with a decimal point:


  10. Joh says:

    Hello Patrick, Thanks a lot for this tool is very helpful, Works like a charm, I just have one little doubt.

    I´m using the formula =Microsofttranslate(A1,”es”,”en”) to translate the text from spanish to english, however I was wondering if is there a method for the formula for it to automatically detects the source language and translate to the specified. i.e. =Microsofttranslate(A1,”en”) something like this.

    Thank you very much in advance for your support


Leave a Reply

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

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