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
ExamplesThese examples are in the GettingStarted series workbook. |
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
Else
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
Else
Debug.Print “they have no pets”
End If
End With
End With
Next jo
Else
Debug.Print “There are no families”
End If
End With
End With
job.tearDown
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" ]}} ]}
}
Deserialization
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
_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
Summary
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