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.

Recap

A cJobject tries to emulate what JavaScript objects are like within the confines of VBA syntax and capabilities. Just like in JavaScript, JSON and objects are tightly linked and if you already know some JavaScript you’ll find all this pretty straightforward.


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



		


cJobject structure

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
  • Note that unlike JavaScript, key names are case insensitive, in  keeping with the VBA convention. You cannot have a dot in a key name, as this is used to separate keys.
  • Strictly speaking, JSON keys and string values should be surrounded by double quotes – like this {“key”:”string value”}. In JavaScript this is not a problem, because you can use either single or double quotes to enclose string constants. In VBA you can only use double, so trying to enclose double quotes inside double quotes would have been a pain. My JSONParse() function therefore is able to deal with either single or double quotes round both keys and values. 

So when we look at the results of iterating through our test data, we can see that arrays are not different to other objects.



		


the results



		


Build one manually

From this we can now see how to build a cJobject programatically rather than from JSON



		


the result



		


Memory management

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