size of an array excel - json vba parser

Question

I am using a vba json parser : https://github.com/VBA-tools/VBA-JSON . I want to loop over the elements in the B array but I am unsure how to do this. e.g.

Set Json = JsonConverter.ParseJSON("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")

If you want to get back the number of elements in B how do you do this?

You get back the actual value by doing the following : Json("a")

What does Json("b") give you? "[1,2,3,4]" or "1,2,3,4" or something else? — ChipsLetten
– ChipsLetten, Commented Aug 3, 2015 at 9:41
Json "b" gives you an error . "Run-time error '450'. This is because I need to specify which array I am accessing. So if i do Json("b")(1) I can get back the first element, which is 1. I also tried Len(Json("b)) but it does not work. Trying to figure out how to return the number of elements in the array then i can loop over them .. =/ — jason bourne
– jason bourne, Commented Aug 3, 2015 at 9:47
How were you trying to get Json("b")? Have you tried the usual VBA array size functions? So, LBound(JSon("b")) and UBound(JSon("b"))? Or try Json("b").Count as the docs for vba-json imply it returns a dictionary/collection. — ChipsLetten
– ChipsLetten, Commented Aug 3, 2015 at 9:55
Have you tried to use Do While ... Loop loop till Json("b")(counter) returns no error? — Maciej Los
– Maciej Los, Commented Aug 3, 2015 at 10:02

ChipsLetten · Accepted Answer · 2015-08-03 10:09:40Z

6

The docs for the source vba-json state:

parse JSON and create Dictionary/Collection

So you will get back one of those objects. This seems to work:

Sub testJson()

Dim Json As Object
Set Json = JsonConverter.ParseJson("{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456}}")

Debug.Print Json("a") ' -> 123
Debug.Print Json("b")(2) ' -> 2
Debug.Print Json("c")("d") ' -> 456
Json("c")("e") = 789

Dim var As Object
    ' Get the object from Json
    Set var = Json("b")
    ' Both Dictionary and Collection support the Count property
    Debug.Print var.Count

Dim elem As Variant
    For Each elem In var
        Debug.Print elem
    Next elem

Debug.Print JsonConverter.ConvertToJson(Json)
' -> "{""a"":123,""b"":[1,2,3,4],""c"":{""d"":456,""e"":789}}"

End Sub

The "b" in the Json example returns a collection but for "c" you would get back a dictionary.

answered Aug 3, 2015 at 10:09

ChipsLetten

2,9531 gold badge15 silver badges28 bronze badges

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

2 Comments

jason bourne Over a year ago

Thank you. What i needed. Spent hours for something so simple ¬_¬

user2165379 Over a year ago

@ ChipsLetten Hi ChipsLetten, I try to implement your solution in my own code although I do not succeed. I hope you can take a look at my question here: stackoverflow.com/questions/75596069/… thanks a lot!

Collectives™ on Stack Overflow

size of an array excel - json vba parser

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related