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 “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
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.

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

Author: brucemcp

Leave a Reply

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