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
Pingback: VBA code for Microsoft Text Translator API | Patrick O'Beirne @ sysmod
Hello. Thanks a lot for this gift.
How can I get the Azure key?
https://docs.microsoft.com/en-us/azure/cognitive-services/translator/translator-text-how-to-signup
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.
I added Watch to both oJson and sResponseText and got this in the Watch window:
oJson | Nothing | Object | MSTranslatorV3.MicrosoftTranslatorDetect
sResponseText | [{“language”:”cy”,”score”:1.0,”isTranslationSupported”:true,”isTransliterationSupported”:false,”alternatives”:[{“language”:”pt”,”score”:0.5,”isTranslationSupported”:true, | String | MSTranslatorV3.MicrosoftTranslatorDetect
By the way, I’m using Excel 2019. Seems like the error pops up because oJson is set to Nothing (according to this doc https://docs.microsoft.com/en-us/dotnet/visual-basic/language-reference/error-messages/object-variable-or-with-block-variable-not-set)
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.
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.
Just try this for me:Anywhere you see “on error resume next “, remove it. In the Class too.
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.”
2-May: I updated ClsJSONLib.cls to remove an unnecessary On Error Resume Next. This should make any errors visible.
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…
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(…
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
Sorry, the error is Type mismatch (Run-time error ’13’). Pointing at CDb1(Value) gives = Type mismatch
Thank you! What is the type and value (content) of the variable Value, please?
And what is your Excel locale? US/UK/…?
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!
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?
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
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}]
}]
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
In the source code, you’ll see a function MicrosoftTranslatorDetect()
For automatic recognition, simply pass the text with no From language specified
Function MicrosoftTranslate(ByVal vInputText As Variant, Optional ByVal sLanguageFrom As String = “”, Optional ByVal sLanguageTo As String = “en”)
thank you very much for your help you are awesome!!
Regards
you are awesome!
thank you very much!
Oh forget about my question, I already found the solution
Regards