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
&nbsp; 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.

About brucemcp 225 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.