cJobject deep dive

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.

Private Function getTestData() As cJobject
    
    ' just get some vanilla json data
    Set getTestData = JSONParse("[{'name':'john','age':25},{'name':'mary','age':50}]")

End Function

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

Dim job As cJobject
    Set job = getTestData()
    
    ' stringify
    Debug.Print job.stringify

and we get this

[{"name":"john","age":25},{"name":"mary","age":50} ]

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.

 Dim job As cJobject, jo As cJobject, jp As cJobject
    
    Set job = getTestData()

    ' iterate
    For Each jo In job.children
        ' jo is each array member
        Debug.Print jo.key, jo.childIndex
        For Each jp In jo.children
            ' jp is each property in each object in the array
            Debug.Print jp.key, jp.value, jo.childIndex, jp.fullKey
        Next jp
    Next jo

the results

1              1 
name          john           1            _null.1.name
age            25            1            _null.1.age
2              2 
name          mary           2            _null.2.name
age            50            2            _null.2.age

Build one manually

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

 Set jm = New cJobject
    With jm.init(Nothing)
        'add an item and make it an array
        With .add.addArray
            ' add an array member
            With .add
                ' add an object to this array member
                .add "name", "john"
                .add "age", 25
            End With
            ' add an array member
            With .add
                ' add an object to this array member
                .add "name", "mary"
                .add "age", 50
            End With
        End With
    End With
    Debug.Print jm.stringify

the result

[{"name":"john","age":25},{"name":"mary","age":50} ]

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.

job.tearDown

Now let’s look at some more cJobject topics:

Storing objects as values

Storing other objects in cJobject   If cJobject was just about getting stuff in and out of JSON, then we'd stop here, but you'll find that I use it in pretty much all the VBA examples on this site. This gives me the ability to deal with  structured data in ...

Read More

Navigation

Iteration   As per the examples in cJobject deep dive the most common navigation of the cJobject is by iteration through the children, or by direct access as covered in How to access children. However there are a few other navigation shortcuts, most of which don't  have JavaScript equivalents. For these tests, we'll ...

Read More

How to access children

Children   As mentioned in the introduction, each cJobject is either a key/value pair or is the parent of some more cJobjects. Accessing the children of a cJobject is straightforward, and as close to the JavaScript as possible within the confines and rules of the VBA language syntax. By key , ...

Read More

Access shortcuts

Children shortcuts As covered in How to access children, cJobect children are roughly equivalent to JavaScript objects' children. However the syntax of VBA means that we have to be a little more verbose than JavaScript. To help a bit, here are some shortcuts for accessing property values. This will return the ...

Read More

For more on this see How to use cJobject