javascriptexcelvba32bit-64bitmsscriptcontrol

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 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?


Solution

  • 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 
        '     https://github.com/VBA-tools/VBA-JSON
        '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", "https://www.alphavantage.co/query?" & _
              "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