VBA getting values from a collection?

Question

I am very new to VBA and I can not figure out how to get values from a Collection.

This is my code:

Dim p As Object Set p = JSON.parse(Response.Content) Dim links As Object Set links = p.Item("links")

In the debugger for "links" I see:

enter image description here

I am using this library to parse json : http://www.ediy.co.nz/vbjson-json-parser-library-in-vb6-xidc55680.html

The part I have in json is:

"links":[ { "rel":"next", "href":"www.google.com" } ]

How can I get the value of "rel" here?

Not familiar, but did you try, e.g. debug.print links(1)(1) — Ron Rosenfeld
– Ron Rosenfeld, Commented Oct 7, 2014 at 7:35
@RonRosenfeld I tried Range("A4").Value = links(1)(1) but content seems to be empty. — Koray Tugay
– Koray Tugay, Commented Oct 7, 2014 at 10:56
I'm not familiar with that JSON parser either but anyway. Hopefully you don't have On Error Resume Next set? Have you just tried debug.print links(0) and debug.print links(1) — MarkJ
– MarkJ, Commented Oct 7, 2014 at 11:15
@MarkJ I tried Debug.Print "Hello World" Debug.Print links(1)(1) Debug.Print "Bye" and I am seeing the Hello World and Bye and an empty line in between them. So (1)(1) is evaluated to null I think.. — Koray Tugay
– Koray Tugay, Commented Oct 7, 2014 at 11:19

Bob77 · Accepted Answer · 2014-10-07 18:25:24Z

Don't forget the bang operator, designed for collection access by key:

links(1)!rel

or:

links(1)![rel] 'When there are spaces or reserved words.

Koray Tugay · Accepted Answer · 2014-10-07 11:27:00Z

3

I will answer my own question:

links(1).Item("rel")

worked...

Regards..

answered Oct 7, 2014 at 11:27

Koray Tugay

24k49 gold badges206 silver badges335 bronze badges

4 Comments

Tim Hall Over a year ago

For a shortcut, Item is the default property for a Dictionary, so you can use links(1)("rel").

wqw Over a year ago

Or even shorter with "bang" operator links!1!rel

MAC Over a year ago

what if i only have this [ { "rel":"next", "href":"www.google.com" } ] what should i do without the "links"? @TimHall

Tim Hall Over a year ago

In that case you would use the raw parsed response, p, instead of links and use p(1)("rel") (or from wqw's comment, p!1!rel)

cyboashu · Accepted Answer · 2014-11-12 04:41:01Z

Using JavaScript features of parsing JSON, on top of ScriptControl, we can create a parser in VBA which will list each and every data point inside the JSON. No matter how nested or complex the data structure is, as long as we provide a valid JSON, this parser will return a complete tree structure.

JavaScript’s Eval, getKeys and getProperty methods provide building blocks for validating and reading JSON.

Coupled with a recursive function in VBA we can iterate through all the keys (up to nth level) in a JSON string. Then using a Tree control (used in this article) or a dictionary or even on a simple worksheet, we can arrange the JSON data as required.

You can see the full VBA code here

Collectives™ on Stack Overflow

VBA getting values from a collection?

3 Answers 3

Comments

4 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

Comments

Linked

Related