Need help skipping an empty json array with VBA

Question

I am looping through different process ids to access the data within the json and copy it to my spreadsheet. However, some of the process id contain no data and every time my code gets to these empty arrays I get an error. I have tried different variations of if statements to skip this but still get an error. The empty array is at "expectedRateSetList"

I have tried different variations of if statements to skip this but still get an error. I've tried 'If J is null, if J is nothing, If J is empty" etc but I still can't get it to work. I've also tried "On error go to" but this hasn't worked.

    `````````````
    For l = 2 To last_row(tb, 2)

    Set J = setJSON(url)
    Set J = CallByName(J, "expectedRateSetList", VbGet)  <---This is the array that is empty        
    If J Is Null Then GoTo next_log
    On Error GoTo next_log
    Set J = CallByName(J, "0", VbGet)   
    Set J = CallByName(J, "expectedRateList", VbGet)
    next_log:
    Next l

'json array looks like this:
{"processId":X,"expectedRateSetList":[],"warehouseId":"warehouseX"}

Are you sure J is an array? Set assigns object references, not arrays, and CallByName works with an object instance, not an array - that said 0 is an illegal member name, so that "0" call is guaranteed to fail. What does setJSON actually do/return? What is the specific error you're getting exactly? — Mathieu Guindon
– Mathieu Guindon, Commented Apr 2, 2019 at 21:02
Are you using VBA-Tools/JSON? Then J would be a Dictionary object, and J("expectedRateSetList") should get you another Dictionary; objects are never Null in VB, but they can be Nothing - that said you shouldn't be changing the type and meaning of J like this. Variables are cheap - declare them with an explicit data type, and give them meaningful names! — Mathieu Guindon
– Mathieu Guindon, Commented Apr 2, 2019 at 21:05
Assuming J (well the first one anyway) is a Dictionary, then If J.Count > 0 Then should rid you of that GoTo jump. — Mathieu Guindon
– Mathieu Guindon, Commented Apr 2, 2019 at 21:06
@MathieuGuindon Is correct. I wonder sometimes if this confusion about array comes from the fact the JSON itself has object {} ( unordered set of name/value pairs) and array [] (ordered collection of values) - ecma-international.org/publications/files/ECMA-ST/ECMA-404.pdf These terms are not applied in the same way in VBA implementations where {} is dict and [] is collection. — QHarr
– QHarr, Commented Apr 2, 2019 at 21:46

Mathieu Guindon · Accepted Answer · 2019-04-02 21:22:49Z

1

J is definitely not an array. J is an object, likely a Dictionary. You can check if a dictionary contains any items by querying its Count property - that removes the need for the line label and GoTo jump, at the cost of increased nesting (but then, the loop body should probably be refactored into its own procedure anyway):

    If J.Count > 0 Then
        ' there are items
    End If
Next

Note that CallByName(J, "MemberName", vbGet) can be replaced by late-bound J.MemberName calls - but then again assuming your parsing isn't hand-crafted and you are getting nested dictionaries, that would be J("MemberName"); the property you're actually invoking is the (default) Items property: J.Items("MemberName") is equivalent.

answered Apr 2, 2019 at 21:22

Mathieu Guindon

71.5k8 gold badges113 silver badges250 bronze badges

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

2 Comments

Sergio Martinez Over a year ago

This makes sense. I will definitely try this. Right now they put me on a couple different projects so it may be a bit before I try this.

Mathieu Guindon Over a year ago

You can use the locals toolwindow to inspect the object structure. Good luck!

Collectives™ on Stack Overflow

Need help skipping an empty json array with VBA

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related