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, 22-Feb-2021 remove reference to Excel.ScreenUpdating.
Updated 30-Mar-21 ClsJSONLib.cls to handle wider range of Unicode characters.
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.
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
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!
Thanks for asking Joh, I’m available for hire on projects like that.
Sentiment: 95 %
send me an email, with costs plz!
Thanks for contacting me by email, I’ve sent the quotation.
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.
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.
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 ?
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?
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
Very nicely done! Thanks for sharing.
Thank you patrick but there is issue at line 75 it says path/file access error.
Line 75 in MSTranslatorV3.bas is blank. Please give the source code of the line failing.
Hi Patrick, thanks for sharing this macro, you simplified my life.
Hello Patrick,
I’m having an error at line webRequest.Send (sRequestBody)
the error number is: -2146697211(800c005).
Can you help me?
Did you try what I suggested in the reply to Fahad above on Jan 22, 2018 ?
Have you tried the suggestions you get when you google for that error code, “Error 2146697211 (800c005) The system cannot locate the resource specified” :
https://stackoverflow.com/questions/11726661/error-from-vb-excel-macro-code-msxml3-dll-2146697211-the-system-cannot-locate
https://support.microsoft.com/en-us/help/843499/error-0x8004005-or-error-0x800c0005-error-messages-when-you-scan-for-u
{“Error:”{code”:”401″,”message”:”Access denied due to invalid subscription key or wrong API endpoint. Make sure to provide a valid key for an active subscription and use a correct regional API endpoint for your resource.”}}
I have a key that I just signed up for, I’m eastus, but the API endpoint it gives me looks to match up to yours. I don’t see any field to plug in for the region. Obviously it’s not working though. Does your code default to a region I can’t see, or since I just signed up for a key does it take an hour or so for it to work?
What method are you using to supply the key to get the access token?
Here is what I have:
Dim webRequest As Object ' late binding
Set webRequest = CreateObject("MSXML2.XMLHTTP")
webRequest.Open "POST", AUTH_URI, False
webRequest.setRequestHeader "Content-Type", "application/json"
'The URL query method does not work: sRequest = "?Subscription-Key=" & ACCOUNT_KEY
'{ "statusCode": 401, "message": "Access denied due to missing subscription key. _
Make sure to include subscription key when making requests to an API." }
' so use the Header method:
webRequest.setRequestHeader "Ocp-Apim-Subscription-Key", ACCOUNT_KEY ' Azure
webRequest.send
sResponse = webRequest.responseText
I copied my key into the location you said and ran your test function. That gave me this wrror
If your key is a valid 32-character Azure account key, I don’t know any more than that.
Looks like the token reference is requiring the region to be added to the URL for me. You can supposedly do the global one, but it won’t let me. Here’s the link where you can see to add the region. I just added it to your URI and everything worked great.
https://docs.microsoft.com/en-us/azure/cognitive-services/translator/reference/v3-0-reference
Well, well, thank you, that’s another thing to note. They say “Region is required for the multi-service Text API subscription.” Is that what you are doing?
Hello, when trying to run translator I get the error “Compile error, expected: end of statement”. Can you help me, please?
That’s a compile error which you would get with Debug>Compile before you run the code. You’ll need to attach a screenshot to show where the error has been found.
Please tell me how to check the bug report, no lines are highlighted
Please tell me how to check the bug report, no lines are highlighted
It should highlight the line, like this:
Here is the screen: https://i.postimg.cc/XqRqjzVq/Error.jpg
I see the problem. Read README.TXT. You copied & pasted in the code rather than using File > Import as specified.
———————————————————————————————————-
This is the VBA source code to access the Microsoft Azure Translator V3 API.
Before you begin, sign up to Azure and get your account key from MS.
https://docs.microsoft.com/en-us/azure/cognitive-services/translator/translator-text-how-to-signup
Unzip the files into a folder.
In Excel, create a workbook to host this code. In the Excel VBA Editor, click
File > Import Files and select MSTranslatorV3.bas and clsJSONLib.cls
In Tools > References, check “Microsoft Scripting Runtime”.
In the module MSTranslatorV3, find the line :
Const ACCOUNT_KEY As String = “Your Account Key”
and put your Azure account key in there.
Run the Sub testMicrosoftTranslate() to verify that the code works.
Please post any questions or bug reports to the API blog entry at https://sysmod.wordpress.com/
When ready to put into production, you should change the debug flag to False:
Private Const DEBUG_MODE As Boolean = True
Patrick O’Beirne (@ExcelAnalytics)
Systems Modelling http://www.sysmod.com
http://ie.linkedin.com/in/patrickobeirne
Hi Patrick,
I created the Azure account and have the tokens, but seems like the .zip file, you can not unpack the two files, README.txt and MSTranslatorV3.bas
Is the Zip file faulty or is it some restriction place by Windows 10 security?
Updating my comment earlier, turns out it was the firewall on Windows 10 that was stopping the extract of those two files.
I have manged to get the test Microsoft Translate to work, so I know my Azure account is working correctly and the vba code and your library has been inserted correctly into Excel.
However I was looking at calling the vba code in a formula i Excel, to enter a formula in Cell D2 that would translate a sentence string value stored in cell C2 (this would be in English) and output the result of the translation, that language to be translated into is in cell D1.
So if cell [C2] has the string ‘Have a good day’ and [D1] has the language code ‘CY’ then the translation using your Function in cell [D2] would be ‘Cael diwrnod da’
Is this possible, can you help make this code work like this?
There is a function in MSTranslatorV3.bas
Function MicrosoftTranslate(ByVal vInputText As Variant, Optional ByVal sLanguageFrom As String = “”, _
Optional ByVal sLanguageTo As String = “en”) As Variant
You can call this from a worksheet formula like this:
=MicrosoftTranslate( C2, “en”, D1 )
which gives “Mwynha dy ddiwrnod”