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 VBA in the same way as you can in more modern languages, and also gives me an easy migration path from (and occassionally to) VBA. In fact, although I don’t use it much nowadays, I implemented a cJobject in JavaScript  so that I could port projects to Google Apps Script with minimal changes.
So this means that I needed to store any object (not just stringifiable ones) as the value of a key/value pair in a cJobject.

Example

For the sake of this example, we’ll populate a sheet and store some Excel Sheet objects in a cJobject. Normally I don’t need to do this since I always use data abstraction when interacting with sheets (see  How to use cDataSet) and that has built in methods for converting to and from cJobjects and JSON, but for the sake of an example, let’s try this. We’ll use the test data from cJobject deep dive and write it to a sheet.

First the headings – we’ll call the columns the same thing as the keys of the JSON data.

Dim r As Range
    Set r = Range("Sheet1!a1")
    
    ' write the headings
    For Each jm In job.children(1).children
        r.Offset(, jm.childIndex - 1).value = jm.key
    Next jm

Now the data

For Each jp In job.children
        For Each jm In jp.children
            r.Offset(jp.childIndex, jm.childIndex - 1).value = jm.value
        Next jm
    Next jp

That correctly gives us this

But there’s no guarantee that the properties of each of the objects in the array are each in the same order if they came from some external JSON source, so we need to refine things a little.

Storing objects as values

Now we’ll create a new cJobject in which we store the Excel Range object at which we stored each column heading. Each property in this cJobject will have a key of the column name. Like this we can retrieve the address of the column header later when we process the data

 ' write the headings
    Set headings = New cJobject
    With headings.init(Nothing)
        For Each jm In job.children(1).children
            headings.add jm.key, r.Offset(, jm.childIndex - 1)
            headings.getObject(jm.key).value = jm.key
        Next jm
    End With

Explanation

When we use the .add method, cJobject will notice that its being asked to store an object rather than a value and treat it specially internally. Here’s what headings looks like when stringified.

{"name":"object:Range","age":"object:Range"}

Of course an Excel range is not stringifiable to JSON, so instead it reports its type against the value. Note that you can store any kind of object or custom class – even another cJobject. It will treat them all the same way.

There are a few methods specially for dealing with objects stored this way –

These are equivalent

set obj = job.child("name").getObject()
set obj = job.getObject("name")

as is

if (job.child("name").isObjValue) then...

and

if (job.isObjValue("name")) then ....

Modifying an object value

When you use .add it replaces the current property value, or creates a new one as appropriate. Objects are the same as regular values. This examples will all create or replace the properties mentioned

job.add "key", value
job.add "key.subkey", value
job.add "key", someobject

Getting back to the example

So now we have the ranges that we stored the columns names in, and all the properties of that object are open to us. 

Let’s take a look

For Each jp In headings.children
        Debug.Print jp.key, jp.getObject().Address
    Next jp

gives

>name       $A$1
age        $B$1

That means we can use the keys in the data to find the correct column to store each data value against by referencing the range object stored in the cJobject

 ' now the data
    For Each jp In job.children
        For Each jm In jp.children
            headings.getObject(jm.key).Offset(jp.childIndex).value = jm.value
        Next jm
    Next jp

which gives us the correct result

Shuffle

To test, we’ll shuffle the order of one of the objects in the test data like this

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

In our first example, the data for mary would have ended up in the wrong column, but since we are now storing the column range keyed by property name, we’ll always end up in the right place regardless of the incoming order. And here’s the result

Ad hoc classes

Using cJobject means that I can get many of the benefits of classes without bothering to make a class, as well as JSON and recursion by default for every data structure and the ability to create objects on the fly. 

Now let’s look at some more cJobject topics.

For more on this see How to use cJobject