Excel VBA-JSON: How do loop through an array inside a JSON object?

Question

Consider this JSON object:

{
"fileName": "Batch_01032023_SakerItemData.xlsx",
"fileLocation": "C:\\Temp",
"message": "There are 3 errors. Please correct and try again.",
"error": [
    "{Item} failed validation:Item is required.:8",
    "{Type} failed validation:Type is required.:8",
    "{Class} failed validation:Class is required.:8"
]

}

I am using the JsonConverter from this repo https://github.com/VBA-tools/VBA-JSON

Consider this VBA code:

    Dim jsonObject As Object, item As Object
    Dim objHTTP As Object
    Dim url    As String
    Dim result As String
    Dim async  As Boolean  
    Dim body   As String

    body = "{""fileLocation"":""{fileLocation}""}"
    body = Replace(body, "{fileLocation}", Replace(fileLocation, "\", "\\"))
    Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
    
    With objHTTP
        .Open "POST", url, async
        .SetRequestHeader "Content-Type", "application/json"
        .SetRequestHeader "Accept", "application/json"
        .SetRequestHeader "Authorization", "Basic " + _
                          Base64Encode(authUser + ":" + authPassword)
        .Send body
        .waitForResponse
        result = .responseText
        
    End With
    
    Set jsonObject = ParseJson(result)
    
    *** What is the syntax here to loop through error object? ****
    For Each item In jsonObject("error")(1)
       
      
    Next

this line Set jsonObject = ParseJson(result) does not throw an error and seems to work, yet when I get to the 'for each' loop, I get Error # 424 'Object Required'.

My question is this: How can I loop through the 'error' array in the 'jsonObject' so that I can display the validation errors to the user? The error array is dynamic.

Answer 1

The key error returns a Collection, so first assign it to a variable declared as Collection...

Dim col As VBA.Collection
Set col = jsonObject("error")

Then loop through each item in the collection...

Dim itm As Variant
For Each itm In col
    Debug.Print itm
Next itm