Using jSon/javaScript like structures in VBA

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….
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
in Google Apps Script (javascript)
in VBA
Which gives us this output… 
100, fred, 20 engineer mathematician cook
To convert to jSon In javaScript
Which gives this…
And back again…
In JavaScript
in VBA
check by stringifying again
Which again gives..

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
Here’s an extract of the output
For more information on cJobject and to see more usage examples, and to see how you can  see Excel Liberation.

About brucemcp 223 Articles
I am a Google Developer Expert and decided to investigate Google Apps Script in my spare time. The more I investigated the more content I created so this site is extremely rich. Now, in 2019, a lot of things have disappeared or don’t work anymore due to Google having retired some stuff. I am however leaving things as is and where I came across some deprecated stuff, I have indicated it. I decided to write a book about it and to also create videos to teach developers who want to learn Google Apps Script. If you find the material contained in this site useful, you can support me by buying my books and or videos.

Be the first to comment

Leave a Reply

Your email address will not be published.


15 + fifteen =