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….
Dim person As New cJobject
With person.init(Nothing)
    .add “key”, 100
    .add “”, “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
in Google Apps Script (javascript)
    Logger.log (person.key + “,” + + “,” + 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(“”); “,”; .toString(“details.age”)
        For Each j In .child(“list”).children
            Debug.Print j.toString()
        Next j
    End With
Which gives us this output…
To convert to jSon
In javaScript
   var s = JSON.stringify (person);
   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(“”)
                        Debug.Print procedure.stringify (True)
                    Next procedure
                End If
            Next module
        Next project
    End With
End Sub
Here’s an extract of the output
Module colorizing      
         “declaration”:”Private Sub applyColors(colorColumn As String)”
Module colorizing
         “declaration”:”Public Sub storeOriginalColors()”
Module colorizing     
         “declaration”:”Public Sub buildSwatch()”
For more information on cJobject and to see more usage examples, and to see how you can  see Excel Liberation.

(function() { var po = document.createElement(‘script’); po.type = ‘text/javascript’; po.async = true; po.src = ‘’; var s = document.getElementsByTagName(‘script’)[0]; s.parentNode.insertBefore(po, s); })();

Author: bm082975

Leave a Reply

Your email address will not be published. Required fields are marked *