What can you learn here?

  • Get started with cJobject
  • Short code snippets
  • Abbreviated explanations

Quick examples (cDataset.xlsm)
This is intended to be a fasttrack to get started with the cJobject that are used throughout the examples on this site.  These classes are used as general purpose hierarchical object, to simulate the capabilities of associative arrays and key value pairs in javaScript, and to give a simple way of serializing and deserializing jSon data in VBA.

What to download

All the examples contain all the classes needed for them to work and all projects can be found here.

If you just want the main utility classes used throughout this site, in the Downloads section Data Mapnipulation includes everything you need including some examples.

If you are already familiar with JavaScript, you can head over to cJobject and javascript equivalents to see how javaScript objects and cJobject compare.

Why do we need cJobject

A big focus on this site is about how to communicate with web services and generally free up Excel data from the desktop. Nowadays, most Web enabled languages such as javaScript have the capability to build heirarchical objects built right into their syntax.
These kind of objects are very jSon friendly and therefore can be easily serialized and interchanged between web services across language boundaries.
Although VBA is vaguely XML friendly, it is not jSon friendly and does not have the kind of associative arrays that make other languages so flexible.  So the cJobject class is an attempt to give VBA some of that flexibility and to make it easy to communicate with other, more capable languages.
Note also that the cJobject is very tightly linked to the cDataSet (which knows how to convert Excel tables to and from cJobjects), so together this pair make a very strong team. If you want to see how to do this in javaScript (Google apps script) see cJobject Class in Google Apps Script.
See this post for some more info cJobject background.

A Primer

A few slides to get the flavor of cJobject

Javascript like objects and JSON processing in VBA from Bruce McPherson


These examples are in the GettingStarted series workbook.
Public Sub testcJobject1()
Dim job As cJobject
Set job = New cJobject
With job
.init Nothing, “our first cJobject”
With .add(“bill”, “founder”)
With .add(“kids”)
With .AddArray
.add , “mary”
.add , “janet”
End With
End With
End With
End With
Debug.Print job.Serialize
Debug.Print job.formatData
End Sub
This is similar to the example in Getting Started with Classes , but this time we don’t need to create a custom class, since we will use the general hierarchical capabilities of cJobject to create one ‘on the fly’. Serializing the object gives us a nice jSon string that we can exchange with webservices or another language.
{“our first cJobject”:{“bill”:{“kids”:[“mary”,”janet” ]}}}
This could be used directly in javaScript, or passed to a web service
var family = {“our first cJobject”:{“bill”:{“kids”:[“mary”,”janet” ]}}</span?
} ;

Formatting out each element of the structure gives us this
job.fullKey                      job.toString
our first cJobject                
our first cJobject.bill                founder
our first cJobject.bill.kids      
our first cJobject.bill.kids.1          mary
our first cJobject.bill.kids.2          janet
Let’s look at something more complex.  Note that the cJobject can deal with arrays as well as key/value pairs.
Private Function getFlintsoneCharacters() As cJobject
Dim job As cJobject
Set job = New cJobject

With job.init(Nothing, “Flintstone Characters”).add(“families”).addArray

With .add.add(“family”)
.add “name”, “FlintStone”
.add “wife”, “Wilma”
.add “husband”, “Fred”

With .add(“kids”).addArray
.add , “Pebbles”
End With

With .add(“pets”).addArray
.add “saber tooth tiger”, “Baby Puss”
.add “dinosaur type dog thing”, “Dino”
.add “dodo bird”, “Doozy”
End With
End With

With .add.add(“family”)
.add “name”, “Rubble”
.add “wife”, “Betty”
.add “husband”, “Barney”

With .add(“kids”).addArray
.add , “Bam Bam”
End With

With .add(“pets”).addArray
.add “kangaroo type thing”, “Hoppy”
End With

End With
End With
Set getFlintsoneCharacters = job
End Function

.. when you play this out

Private Sub testcJobject2()
Dim job As cJobject, jo As cJobject, joc As cJobject
Set job = getFlintsoneCharacters()
With job
With .child(“families”)
If .hasChildren Then
Debug.Print “There are ” & .children.count & ” families”

For Each jo In .children
With jo.child(“family”)
Debug.Print “the family name of family ” _
& .parent.childIndex; ” is ” & .child(“name”).toString
Debug.Print .child(“wife”).toString & _
” is married to ” & .child(“husband”).toString
With .child(“kids”)
If .hasChildren Then
For Each joc In .children
With joc
Debug.Print ” child ” & .childIndex & ” is “; .toString
End With
Next joc
Debug.Print “they have no children”
End If
End With
With .child(“pets”)
If .hasChildren Then
For Each joc In .children
With joc
Debug.Print ” pet ” & .childIndex & ” is a “; .key & ” called “; .toString
End With
Next joc
Debug.Print “they have no pets”
End If
End With
End With
Next jo
Debug.Print “There are no families”
End If
End With
End With
End Sub

..you get this .. can you figure it out from the code?

There are 2 families
the family name of family 1 is FlintStone
Wilma is married to Fred
 child 1 is Pebbles
 pet 1 is a saber tooth tiger called Baby Puss
 pet 2 is a dinosaur type dog thing called Dino
 pet 3 is a dodo bird called Doozy
the family name of family 2 is Rubble
Betty is married to Barney
 child 1 is Bam Bam
 pet 1 is a kangaroo type thing called Hoppy
.. the cJobject looks like this
Flintstone Characters  
Flintstone Characters.families   
Flintstone Characters.families.family                  Flintstone Characters.families.family.1                Flintstone Characters.families.family.1.name   FlintStone
Flintstone Characters.families.family.1.wife   Wilma
Flintstone Characters.families.family.1.husband   Fred
Flintstone Characters.families.family.1.kids           
Flintstone Characters.families.family.1.kids.1 Pebbles
Flintstone Characters.families.family.1.pets           
Flintstone Characters.families.family.1.pets.saber tooth tiger   Baby Puss
Flintstone Characters.families.family.1.pets.dinosaur type dog thing  Dino
Flintstone Characters.families.family.1.pets.dodo bird     Doozy
Flintstone Characters.families.family.2                Flintstone Characters.families.family.2.name     Rubble
Flintstone Characters.families.family.2.wife     Betty
Flintstone Characters.families.family.2.husband     Barney
Flintstone Characters.families.family.2.kids           
Flintstone Characters.families.family.2.kids.1       Bam Bam
Flintstone Characters.families.family.2.pets           Flintstone Characters.families.family.2.pets.kangaroo type thing Hoppy

.. and it serialized to this jSon string

{"Flintstone Characters": {"families":[{"family":{"name":"FlintStone","wife":"Wilma", "husband":"Fred","kids": ["Pebbles" ],"pets": ["Baby Puss","Dino","Doozy" ]},{"name": "Rubble","wife":"Betty","husband": "Barney","kids":["Bam Bam" ], "pets":["Hoppy" ]}} ]}


And of course we can create a cJobject from a jSon string that could have been received from a web service
In this case we will create a cJobject and serialize it to jSon, which would look like any jSon string received from another application. We can deSerialize it back to a cJobject, the serialize it again to see what it looks like – it should be the same as the string we started with.
Private Sub testcJobject3()
    Dim job As cJobject, jo As cJobject, s As String, joc As cJobject
    Set jo = New cJobject
    Set job = testcJobject1()
    s = job.Serialize
    Debug.Print s
    Set joc = jo.deSerialize(s).Children(1)
    Debug.Print joc.Serialize
    Debug.Print joc.formatData
End Sub

.. the original

{"our first cJobject":{"bill":{"kids":["mary","janet" ]}}}

.. the cJobject looks like this

our first cJobject                
our first cJobject.bill           founder
our first cJobject.bill.kids      
our first cJobject.bill.kids.1    mary
our first cJobject.bill.kids.2    janet

.. deserialize and reserialize

{"our first cJobject":{"bill":{"kids":["mary","janet" ]}}}

.. and the cJobject looks like this

_deserialization.our first cJobject                
_deserialization.our first cJobject.bill           
_deserialization.our first cJobject.bill.kids      
_deserialization.our first cJobject.bill.kids.1    mary
_deserialization.our first cJobject.bill.kids.2    janet


Using cJobject brings in some of the capabilities of languages like javaScript, but more importantly it allows Vba to talk to web services and easily decode and access the results. For more jSon articles please see json. We look forward to your comments on our forum.
Now try cJobject deep dive