Hello everyone,
I try to run the VBA code as below but it is compile error "Run time error "424":Object require" on Set GetJSON = JSONConverter.ParseJson(.responseText)
Appreciate someone can guide me to find out what is the error mean for?


Sub Test()
    Const sURL As String = "https://eservices.mas.gov.sg/api/action/datastore/search.json?resource_id=95932927-c8bc-4e7a-b484-68a66a24edfe&fields=end_of_day,jpy_sgd_100&limit=10&sort=end_of_day%20desc"
    Dim a, ws As Worksheet, json As Object, colData As Collection, m As Long
    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set json = GetJSON(sURL)
    Set colData = json("result")("records")
    a = CollectionToArray(colData)
    With ws
        .Cells.ClearContents
        .Range("A1").Resize(1, 2).Value = Array("End Of Day", "Amount")
        .Range("A2").Resize(UBound(a, 1), UBound(a, 2)).Value = a
    End With
    Set json = Nothing: Set colData = Nothing
End Sub

Function GetJSON(ByVal sURL As String) As Object
    Dim http As MSXML2.XMLHTTP60
    Set http = New MSXML2.XMLHTTP60
    With http
        .Open "Get", sURL, False
        .setRequestHeader "User-Agent", "Mozilla/5.0"
        .send
        Set GetJSON = JSONConverter.ParseJson(.responseText)
    End With
End Function

Function CollectionToArray(c As Collection) As Variant()
    Dim a(), i As Long, j As Long
    ReDim a(1 To c.Count, 1 To 2)
    For i = 1 To c.Count
        a(i, 1) = c.Item(i)("end_of_day")
        a(i, 2) = c.Item(i)("jpy_sgd_100")
    Next i
    CollectionToArray = a
End Function