Parse JSON string and looping through all the element in dictionary using VBA

Question

I have been using JsonConverter to parse JSONfile and then using following code to extract all the "label" and sub "values" from dictionary into my userform combobox.

Set JSP = JsonConverter.ParseJson(JSONtxtString)

For Each A In JSP
    Debug.Print A
    If Not IsObject(JSP(A)) Then
        Debug.Print JSP(A)
        
    Else
        For Each B In JSP(A)
            If Not IsObject(JSP(B)) Then
                'Debug.Print B("label")
                    Me.AttributeComBo.AddItem B("label")
                    Me.ConditionBox.AddItem B("label")

                If B("type") = "selectboxes" Or B("type") = "select" Then
                    For Each C In B("values")
                       'Debug.Print C("label")
                       Me.CBSubValues.AddItem C("label")
                    Next C
                End If
            Else
            End If
        Next B
    End If
Next A

however I could not get the values("label") from "Favorate color". from the following JSON.

{"components": [
        {
            "label": "Family Name",
            "tableView": true,
            "key": "familyName",
            "type": "textfield",
            "input": true
        },
        {
            "label": "Amount of Money",
            "mask": false,
            "tableView": false,
            "delimiter": false,
            "requireDecimal": false,
            "inputFormat": "plain",
            "truncateMultipleSpaces": false,
            "key": "amountOfMoney",
            "type": "number",
            "input": true
        },
        {
            "label": "I hereby confirm",
            "tableView": false,
            "key": "iHerebyConfirm",
            "type": "checkbox",
            "input": true,
            "defaultValue": false
        },
        {
            "label": "Which Cities do you like",
            "optionsLabelPosition": "right",
            "tableView": false,
            "values": [
                {
                    "label": "New York",
                    "value": "newNew YorkYork",
                    "shortcut": ""
                },
                {
                    "label": "Munich",
                    "value": "Munich",
                    "shortcut": ""
                },
                {
                    "label": "Paris",
                    "value": "Paris",
                    "shortcut": ""
                },
                {
                    "label": "Hongkong",
                    "value": "Hongkong",
                    "shortcut": ""
                },
                {
                    "label": "Mumbai",
                    "value": "Mumbai",
                    "shortcut": ""
                }
            ],
            "key": "whichCitiesDoYouLike",
            "type": "selectboxes",
            "input": true,
            "inputType": "checkbox"
        },
        {
            "label": "Favorite color",
            "widget": "choicesjs",
            "tableView": true,
            "data": {
                "values": [
                    {
                        "label": "black",
                        "value": "black"
                    },
                    {
                        "label": "white",
                        "value": "white"
                    },
                    {
                        "label": "blue",
                        "value": "blue"
                    },
                    {
                        "label": "green",
                        "value": "green"
                    }
                ]
            },
            "key": "favoriteColor",
            "type": "select",
            "input": true
        },
        {
            "type": "button",
            "label": "Submit",
            "key": "submit",
            "disableOnInvalid": true,
            "input": true,
            "tableView": false
        }
    ]
}

I could not get the values i.e black, white, blue, green from label("Favorite color") WHAT IS THE BETTER SOLUTION TO EXTRACT ALL THE VALUES FROM ANY JSON FILE? how can I loop through each object in JSON and extract all the values?

score 1 · Accepted Answer · answered Nov 03 '21 at 18:02

Working with JSON in VBA can be frustrating. I created this answer that I use as my own reference. But more importantly, take the time to clearly identify what parts of the JSON are being referenced.

In your case, the top level is a Collection of components. Each component has a label. A component may have an array of values.

In the case of the "Favorite Color" component though, the values are inside a data Dictionary.

Here is some example code I use to keep it all clear and straight:

Option Explicit
Sub test2(ByRef jsonText As String)

    Dim json As Variant
    Set json = JsonConverter.ParseJson(jsonText)

    Dim components As Collection
    Set components = json("components")
    
    Dim component As Variant
    For Each component In components
        Dim label As String
        label = component("label")
        Debug.Print "Label: " & label
        
        On Error Resume Next
        Dim values As Collection
        Set values = component("values")
        
        Dim data As Dictionary
        Set data = component("data")
        On Error GoTo 0
        
        Dim value As Variant
        If Not values Is Nothing Then
            For Each value In values
                Debug.Print "   Value: " & value("label")
            Next value
        ElseIf Not data Is Nothing Then
            Set values = data("values")
            For Each value In values
                Debug.Print "   Value: " & value("label")
            Next value
        Else
            Debug.Print "   No values"
        End If
        Set values = Nothing
        
    Next component
    
End Sub

Note that the values are not guaranteed to exist in each component. That's why we have to disable/enable error checking.

Parse JSON string and looping through all the element in dictionary using VBA

1 Answers1