You’ll probably now be familiar with cJobject and want to do some fancier things. Here are some examples. You can download a vanilla cJobject workbook from the data access section of the downloads page, or construct your own with the tools in Integrate VBA with Github and this repository.
First we’ll get some test data to play around with.
This is an array of two objects, each of which have a name and age property. The first thing we’ll do is stringify that back to JSON
and we get this
To get the best out of cJobject, you should understand it’s structure. Here’s the rules
- Each object has a key which has a value of either a basic VBA type like string, variant etc., or instead of a value it has a collection of children – which are other cJobects – which themselves follow these rules and so on forever. This means that a cJobject is eventually stringifiable into a JSON string. It is possible for a cJobject value to be any other kind of object also – but we’ll deal with that advanced later.
- Each object has a childIndex, starting at one and can be accessed both by its childIndex number or its key.
- Arrays are a special kind of object whose children have keys equal to their childIndex.
- All cJobjects are iterable
So when we look at the results of iterating through our test data, we can see that arrays are not different to other objects.
Build one manually
From this we can now see how to build a cJobject programatically rather than from JSON
The VBA garbage collector is not good at dealing with doubly linked items, so these kind of objects will persist in memory until Excel exits – that’s one reason why VBA suffers from memory leak problems that some other platforms don’t have. It’s good practice to use the .tearDown method when you are finished with a cJobject, like this, as it will break the back links and alert the garbage collector it’s ready to be recovered.
Now let’s look at some more cJobject topics:
For more on this see How to use cJobject