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.

Tim Williams · Accepted Answer · 2021-02-06 07:52:12Z

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

edited Feb 6, 2021 at 7:52

answered Feb 5, 2021 at 23:15

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related