64 Bit Excel Vba to Call a Javascript Function
I Need to Use a Vba Scriptcontrol Object to Call a Javascript Function, but It Gave Me a "Class Not Registered" Error. I Have Added Microsoft Script Control...
I need to use a VBA ScriptControl object to call a JavaScript function , but it gave me a "Class Not Registered" error. I have added Microsoft Script Control 1.0 from Tools->References I need to call JavaScript to get a JSON object from this Rest API to calculate values in an Excel Macro.
This post told me that ScriptControl is for use in 32 bit only. I am using 64 bit Excel. I also tried using the method mentioned in this link but it didn't work since VBA doesn't recognise the ActiveXObject
My Excel VBA code to call a simple JS function:
Private Sub CommandButton1_Click()
Dim jsObj As MSScriptControl.ScriptControl, result As Integer
Set jsObj = CreateObject("MSScriptControl.ScriptControl")
jsObj.Language = "JScript"
With jsObj
.AddCode ("function prod1(a,b){return a*b;}")
result = .Run("prod1", 2, 3)
End With
MsgBox result
End Sub
I am getting a class not registered error for the line Set jsObj = CreateObject("MSScriptControl.ScriptControl") Is there an alternate way to call a JavaScript function from VBA? Or am I missing something?
1 Answer
There's no need for a ScriptControl object: you can use XMLHTTP and VBA-JSON.
For example:
Public Sub Tester()
'Import the "JsonConverter.bas" file from
'
'and add a reference to the Microsoft Scripting Runtime library
Dim http As Object, JSON As Object, i As Integer, o As Object, k
Set http = CreateObject("MSXML2.XMLHTTP")
http.Open "GET", "" & _
"function=CURRENCY_EXCHANGE_RATE&from_currency=USD" & _
"&to_currency=JPY&apikey=demo", False
http.Send
Debug.Print http.responseText
Debug.Print "-----------------------------------"
Set JSON = ParseJson(http.responseText)
Set o = JSON("Realtime Currency Exchange Rate")
For Each k In o.keys
Debug.Print k, o(k)
Next k
End Sub
Maya is a hardware enthusiast who tests and reviews smart home devices, smartphones, wearables, and audio gear. She focuses on practical consumer value and build quality.
