jSon /VBA functions have always been the most popular topic on the Excel Liberation site and forum.
The interesting part about this though is not so much about parsing and stringifying jSon data (which after all is just a simple matter of decoding and formatting), but rather about what to do next. In javaScript, creating objects dynamically is probably its most powerful feature, but VBA doesn’t have anything like that.
Before I started to integrate jSon and VBA, probably a couple of years ago now, I realized that I first needed to create such a capability for VBA. You can read about the cJobject in detail here, but essentially it allows you to recreate something like this sample
In javaScript…
var person = {}; person.key = 100; person.details= { name:’fred’, age:20 }; person.list = [‘engineer’,’mathematician’,’cook’];
and in VBA….
Dim person As New cJobject With person.init(Nothing) .add "key", 100 .add "details.name", "fred" .add "details.age", 20 With .add("list").addArray .add , "engineer" .add , "mathematician" .add , "cook" End With End With
Sure you can create a class, and subclasses in VBA and sub-sub classes and so on, but not on the fly – jSon needs the ability to dynamically create objects like this. The cJobject allows
you to do exactly this in VBA. Although not strictly necessary, there is also Google Apps Script version of cJobject to permit VBA to GAS migration with minimal changes, The syntax of VBA
make it a little more clumsy than the equivalent javaScript, but nevertheless it remains recognizable. Here’s how to access and print this object
you to do exactly this in VBA. Although not strictly necessary, there is also Google Apps Script version of cJobject to permit VBA to GAS migration with minimal changes, The syntax of VBA
make it a little more clumsy than the equivalent javaScript, but nevertheless it remains recognizable. Here’s how to access and print this object
in Google Apps Script (javascript)
Logger.log (person.key + “,” + person.details.name + “,” + person.details.age ); for (var i = 0 ; i < person.list.length;i++) { Logger.log (person.list[i].toString()); }
in VBA
With person Debug.Print .toString("key"); ","; .toString("details.name"); ","; .toString("details.age") For Each j In .child("list").children Debug.Print j.toString() Next j End With
Which gives us this output…
100, fred, 20 engineer mathematician cook
To convert to jSon In javaScript
var s = JSON.stringify (person);
In VBA
s = person.stringify() .... JSONstringify(person) also works
Which gives this…
{"key":100,"details":{"name":"fred","age":20},"list":["engineer","mathematician","cook"]}
And back again…
In JavaScript
var person = JSON.parse(s);
in VBA
Set p = JSONParse(s)
check by stringifying again
debug.print. p.stringify
Which again gives..
{"key":100,"details":{"name":"fred","age":20},"list":["engineer","mathematician","cook"]}
So, forgetting all about jSon for a moment, the cJobject is itself a very useful structure indeed. Many of the projects I do are only possible because of recursive friendliness of the cJobject. As an example, let’s say you wanted to show the calls for all procedure in all standard modules in all currently open projects. Using the libraries in cDataSet.xlsm – downloadable here – this is all the code you need
Private Sub getProcData() Dim project As cJobject, module As cJobject, procedure As cJobject With projectsToJobject() For Each project In .child("projects").children For Each module In project.child("project.modules").children If module.toString("module.kind") = "StdModule" Then For Each procedure In module.child("module.procedures").children Debug.Print “Module “;module.toString("module.name") Debug.Print procedure.stringify (True) Next procedure End If Next module Next project .tearDown End With End Sub
Here’s an extract of the output
Module colorizing { "procedure":{ "name":"applyColors", "scope":"Private", "kind":"Sub", "returns":"void", "lineCount":24, "declaration":"Private Sub applyColors(colorColumn As String)" } }
Module colorizing { "procedure":{ "name":"storeOriginalColors", "scope":"Public", "kind":"Sub", "returns":"void", "lineCount":25, "declaration":"Public Sub storeOriginalColors()" } }
Module colorizing { "procedure":{ "name":"buildSwatch", "scope":"Public", "kind":"Sub", "returns":"void", "lineCount":44, "declaration":"Public Sub buildSwatch()" } }
For more information on cJobject and to see more usage examples, and to see how you can see Excel Liberation.