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.


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.


Now let's look at some more cJobject topics.

For help and more information join our communityfollow the blog or follow me on twitter .  For more on this see How to use cJobject