excel - Need help skipping a null element in an json array with VBA

Question

i have an issue with a parsed json that contains null members which i need to skip in my loop. My Json looks something like this:

[
    {
        "id": 72936,
        "count": 27,
    },
    null,
    {
        "id": 70438,
        "count": 18,
    },
    {
        "id": 20003,
        "count": 3,
    },
    null,
    null,
    null,
    null,
    {
        "id": 23045,
        "count": 1,
    },
    {
        "id": 23045,
        "count": 1,
    }
]

I tried different if statements which i found via google, but nothing worked for me, it just ends in an error. See my Code as example:

For j = 1 To Json.Count + 1
   If Not IsNull(Json(j)) Then
      If id = Json(j)("id") Then
         value = Json(j)("count")
         Exit For
      End If
   End If
Next j

IsNull, IsEmpty, IsError or other tries with Json(j) <> Null doesn't work here. Would be very happy when someone know how to fix this issue.

question from:https://stackoverflow.com/questions/66064551/need-help-skipping-a-null-element-in-an-json-array-with-vba

Answer 1

When posting sample json, please take a minute to check it's valid.

Your problem is not the null testing, but an off-by-one error in your loop:

For j = 1 To Json.Count + 1

Here Json is a 1-based Collection object, so adding one to the count will give you "Subscript out of range" when you get to the last iteration.

This gave the expected output:

Dim jso As Object, o

'loading from a cell for testing...
Set jso = JsonConverter.ParseJson(Sheet2.Range("B1").Value)

For Each o In jso
    Debug.Print IsNull(o)
Next o