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.

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

38 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

      Variant():fr–>en
      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 http://www.oaltd.co.uk/excelprogref/ch22/ProgRefCh22.htm

    • 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:

        [{“language”:”cy”,”score”:1.0,”isTranslationSupported”:true,”isTransliterationSupported”:false,
        “alternatives”:[
        {“language”:”pt”,”score”:0.5,”isTranslationSupported”:true,”isTransliterationSupported”:false},
        {“language”:”it”,”score”:0.5,”isTranslationSupported”:true,”isTransliterationSupported”:false}]
        }]

  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

    Regards

  11. Joh says:

    Hey Patrick it´s me again, hope you are well,

    You have by any chance something like this great translator, but for Text Analytics? (https://azure.microsoft.com/en-us/services/cognitive-services/text-analytics/)

    I´m thinking something like the example with translator but something like having the text in A1, and then the language in B1, the keywords in C1, Sentiment percentage in D1, entities in E1 and Named enity recognition in F1. Thank you in advance for your support!!!

    Regards!

  12. Joh says:

    send me an email, with costs plz!

  13. JT Jacoby says:

    Hello Patrick ! Thanks a lot for this solution, it is working very good. Then, I had a question, regarding the performance of this. I had this part of code before without need of Vba-Json library.

    Tlang.Open “POST”, sHost, False ‘open connection to “Translator Text API” POST command required
    Tlang.setRequestHeader “Ocp-Apim-Subscription-Key”, zKey ‘authentication Required
    Tlang.setRequestHeader “Content-type”, “Application/json” ‘Content-type Required
    Tlang.send zTTxt ‘format = [{“text”:”Bonjour utilisateur”}]
    Tlang.waitForResponse ‘the response takes 1+ seconds needs wait or delay command or results will fail as response has not returned data yet
    ‘Debug.Print Tlang.GetAllResponseHeaders

    Obviously, it is not so precise as your solution, and in caused issues with special french characters on few computers, but it was far much quicker, especially for mass translations. So any clue about how to speed up your solution ?

    Thanks !

    Jean-Thomas

    • That’s V2 of the API. I have now moved to V3 as recommended by MS, although they have retained V2 for a while.

      • JT Jacoby says:

        No, that’s the V3 as well, full code below if it can help :

        Thanks,

        Function MStranslate(ByVal sText As String, Optional ByVal sLanguageFrom As String = “”, _
        Optional ByVal sLanguageTo As String = “fr”) As String
        ‘Single step translation code
        If Len(sText) > 0 Then ‘if blank do nothing return the blank value
        Dim sHost As String
        Dim zTTxt As String
        Dim zKey As String
        Dim startpl, endpl As Integer

        zKey = “xxxxxxxxxx” ‘authentication Key from subscription
        sHost = “https://api.cognitive.microsofttranslator.com/translate?api-version=3.0” ‘required link for authentication
        sHost = sHost & “&from=” & sLanguageFrom & “&to=FR” ‘determine language from and langauge to
        zTTxt = “[{“”text””:” & “””” & sText & “””}]” ‘JSON format spcific requirement [{“text”:”value”}] max 5000 characters

        Dim Tlang As Object
        Set Tlang = CreateObject(“WinHttp.WinHttpRequest.5.1”) ‘need to add reference libary “Microsft WinHTTP Service,Version 5.1”
        Tlang.Open “POST”, sHost, False ‘open connection to “Translator Text API” POST command required
        Tlang.setRequestHeader “Ocp-Apim-Subscription-Key”, zKey ‘authentication Required
        Tlang.setRequestHeader “Content-type”, “Application/json” ‘Content-type Required
        Tlang.send zTTxt ‘format = [{“text”:”Bonjour utilisateur”}]
        Tlang.waitForResponse ‘the response takes 1+ seconds needs wait or delay command or results will fail as response has not returned data yet
        ‘Debug.Print Tlang.GetAllResponseHeaders

        startpl = 28 ‘if you use auto languae detect you will need to adjust this number to “69” or greater
        endpl = InStr(startpl, Tlang.responseText, “”””) ‘[{“translations”:[{“text”:”Hello user”,”to”:”en”}]}]
        MStranslate = Mid(Tlang.responseText, startpl, endpl – startpl) ‘Parse out translated text
        Tlang.abort
        Else
        MStranslate = sText ‘if blank do nothing return the blank value
        End If
        End Function

      • The reason my code is slower is because there is a Sleep of 1 second for each call to respect the MS limit of 500 characters per second. If you’re passing much less than that, you could change the Sleep microseconds to the number of characters times 2.

      • JT Jacoby says:

        Hello Patrick, Ok, I see, that’s perfect ! Actually we are using large excel templates, as for example 300-500 occurrences in a loop to translate. Number of characters par occurrences are most of the time not too high but can reach the limit of 5000 characters, but event with no sleep of 1 second, it seems to work correctly. Where did you see such limit ?

  14. Oh, excuse me, when I saw the Ocp-Apim subscription key I thought it was V2. I’ll look at this later. To help me, can you tell me where is your solution quicker? In getting the response? In parsing the response text?

  15. https://docs.microsoft.com/en-us/azure/cognitive-services/translator/request-limits
    Character limits per hour
    Your character limit per hour is based on your Translator Text subscription tier. The hourly quota should be consumed evenly throughout the hour. If you reach or surpass these limits, or send too large of a portion of the quota in a short period of time, you’ll likely receive an out of quota response. There are no limits on concurrent requests.

    Tier Character limit
    F0 2 million characters per hour

    =555 char/sec

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