Parse API response with JsonConverter: Run-time error '438': Object doesn't support this property or method

Question

I'm trying to write Excel VBA code that calls an API to fetch data, massage the data, then output the cleaned data over a range of cells in the spreadsheet.

I run into an error while trying to parse the API response with JsonConverter.
I followed the instructions at https://github.com/VBA-tools/VBA-JSON to install VBA-JSON, and imported JsonConverter.bas into my script, and added the reference to "Microsoft Scripting Runtime".
I installed Tablacus as a 64-bit ScriptControl.

Here's the beginning of the code, and where it hits the error:

Function IsValidJson(jsonString As String) As Boolean
    Dim regex As Object
    Set regex = CreateObject("VBScript.RegExp")
    regex.Pattern = "^[\s\S]*\{[\s\S]*\}[\s\S]*$"
    IsValidJson = regex.Test(jsonString)
End Function

Sub RefreshData()
    Dim url As String
    Dim http As Object
    Dim json As Object
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim trading_pairs() As String
    Dim trading_pairs_eth() As String
    Dim trading_pairs_usd() As String
    Dim final_pairs() As String
    Dim final_prices() As Double
    Dim final_volumes() As Double
    Dim pair As String
    Dim volume As Double
    Dim price As Double
    Dim row As Integer
    Dim JsonConverter As Object
    Dim dict As New Dictionary
    
    Set http = CreateObject("MSXML2.XMLHTTP")
    Set JsonConverter = CreateObject("ScriptControl")
    JsonConverter.Language = "JScript"
    JsonConverter.AddCode "function parseJson(jsonString) { return JSON.parse(jsonString); }"
    
    ' Fetch all trading pairs with BTC as the quote currency
    url = "https://api.pro.coinbase.com/products/BTC-USD/book"
    http.Open "GET", url, False
    http.send
    If IsValidJson(http.responseText) Then
        Set json = JsonConverter.ParseJson(http.responseText)  ' <- error happens here
        ReDim trading_pairs(json("bids").Count - 1)
        For i = 0 To json("bids").Count - 1
            trading_pairs(i) = json("bids")(i)("price")
        Next i
    Else
        MsgBox "Invalid JSON string"
        Exit Sub
    End If

This is part of the whole script, it's quite long.

The line Set json = JsonConverter.ParseJson(http.responseText) is where it is throwing error 438.

I have a IsValidJson function validating the "http.responseText", before attempting to parse it with JsonConverter.
It passes the validation check, but still throws error 438 when trying to parse.

Code originally generated by ChatGPT.

Answer 1

The JSON-Converter you linked isn't a class that needs to be instantiated. The code that you display looks like a mixture of (at least) two different things.

The code you need to use is rather simple.

Sub RefreshData()
    Const url = "https://api.pro.coinbase.com/products/BTC-USD/book"

    Dim http As Object
    Set http = CreateObject("MSXML2.XMLHTTP")

    ' Fetch all trading pairs with BTC as the quote currency
    http.Open "GET", url, False
    http.send

    Set json = JsonConverter.ParseJson(http.responseText)
    (now your work starts)
End Sub

The call JsonConverter.ParseJson(http.responseText) is the call to the function ParseJson of the module JsonConverter, not an object. You can omit the module name and simply write Set json = ParseJson(http.responseText) - that's exactly the same.

Therefore you need to delete the definition of the variable JsonConverter else the compiler will assume that you want to call a method ParseJSon of that object and therefore you will get the 438 error.

I have no clue why you or ChatGPT want to create a ScriptControl object, remove that line and the assingments to Language and AddCode also.

I am also not sure if the function IsValidJson is doing it's job, I have removed the call to it. The ParseJSon function will raise an error (10001) if the JSON is not valid.

---

Once this works, you need to walk thru the parsed JSon, and your code will raise the next errors soon. Just some hints:

I had a quick look to http.responseText, for me it looked like this:

{"bids":[["36575.59","0.51069628",3]],
 "asks":[["36575.6","0.0266337",2]],
 "sequence":68520027803,
 "auction_mode":false,
 "auction":null,
 "time":"2023-11-18T22:04:21.019956Z"}

After the call to ParseJSon, you have a dictionary as result in variable json (check the content with the debugger). The element bids is an array with 1 element, and this element itself is an array with 3 elements (3 numbers). The JSonConverter converts an array to a Collection. Collections in VBA are 1-based, not 0-based. To get an idea:

    Set json = JsonConverter.ParseJson(http.responseText) 
    Debug.Print http.responseText
    Dim bids As Collection, bidvalues As Collection
    Set bids = json("bids")
    ' You now have a collection with one element.
    Dim i As Long, j As Long
    For i = 1 To bids.Count
        Set bidvalues = bids(i)
        For j = 1 To bidvalues.Count
            Debug.Print i, j, bidvalues(j)
        Next j
    Next i