In javaScript and other languages, creating json representations of an object is a breeze. Something like JSON.stringify (theObject) will do it for you. Aside from liberating data from your application, it makes debugging so much simpler.
Stringifying in VBA
JSON stringification of VBA custom classes is not at all easy. I decided I would see if I could create a way to stringify a VBA class that could be called as a one liner in the same way as in other languages. I already had a whole section on emitting jSon from Excel here and I also had the cJobject , which is a javascript like object implemented in VBA to play with. The final piece of the puzzle was the automatic documenter for VBA which accesses the VBE code.
You can download all this in the cDataSet.xlsm workbook. Here’s how it all fits together.
An example
Let’s say that we want to do something like JSON.stringify (cDataSet) where cDataSet is one of the custom classes used in Data Manipulation Classes – an abstraction of the Excel object data model.
Here’s a one liner to take a populated cDataSet (ds) and print the JSON serialization.
Debug.Print objectStringify(ds)
Here’s the stringify code
Public Function objectStringify(o As Variant, Optional pretty As Boolean = True, _ Optional explodeArrays As Boolean = True, _ Optional explodeCollections As Boolean = False) As String Dim jResult As cJobject ' make this into an jsonifiable object Set jResult = jObjectify(o, explodeArrays, explodeCollections) ' json.stringify it If (Not jResult Is Nothing) Then objectStringify = jResult.stringify(pretty) jResult.tearDown Else objectStringify = "{" & quote("error") & ":" & quote("could not stringify class") & "}" End If End Function
And here’s the result
{ "xLiberation stringified" : { "keepFresh" : false, "keyColumn" : 0, "module" : { "kind" : "ClassModule", "name" : "cDataSet", "properties" : { "columns" : "[object Collection]", "headingRow" : { "module" : { "kind" : "ClassModule", "name" : "cHeadingRow", "properties" : { "headings" : "[object Collection]", "parent" : "[backlink detected to cDataSet]", "where" : "[object Range]" } } }, "headings" : "[object Collection]", "parent" : "[Empty cDataSets]", "rows" : "[object Collection]", "where" : "[object Range]" } }, "name" : "inputdata", "recordFilter" : false, "visibleRowsCount" : 12 } }
Some More examples
Using the Data Manipulation Classes, here’s a selection of calls to serializing various subclasses of cDataSet
Public Function testSerializeClass() ' create a test example object and fill it up with something Dim ds As cDataSet Set ds = New cDataSet ds.populateData wholeSheet("inputdata"), , , , , , True ' make various objects into json strings Debug.Print jsonStringify(ds) Debug.Print jsonStringify(ds.row(1)) Debug.Print jsonStringify(ds.column(3)) Debug.Print jsonStringify(ds.cell(2, 2)) Debug.Print jsonStringify(ds.jObject) Debug.Print jsonStringify(ds.rows) ' clean up ds.tearDown End Function
Eligible properties
The serializer will follow the chain of custom properties referenced until it either hits an inbuilt Excel property (for which it cannot determine the schema, or it hits a non-object value). Only public properties that take no arguments (that should be most) are eligible for objectification. In a future version, I’ll also include public properties whose arguments are all optional.
Nested objects
The serializer is recursive, meaning that it will dig deeper and deeper into the object serializing children objects as well. It uses the (little known/used) VBA CallByName() function. Without this, the class serializer could not have been implemented. CallByName allows you to call a property of a class instance from a variable property name, in other words
Set c = CallByName(instance, propName, VbGet)
The above will execute the VBA equivalent of javaScript instance[propName] – a construct that doesn’t otherwise exist in VBA
Avoiding infinite loops
Since classes can reference other classes recursively there is a possibility of an infinite loop. This is especially true if you have a tree structure with a link back to a parent, and a link from the parent back to its children. The classSerializer gets round this by reporting on objects that it has already seen (giving them a value showing where the back link goes) and stopping following that particular branch.
"parent" : "[backlink detected to cDataSet]"
Arrays
Arrays are handled, but for now, only one dimensional ones. I will add multidimensional support at a later date if there is demand. Whether or not to explode out arrays is selectable by a parameter.
Collections
In this version collections are reported as an internal Excel object – which they are. In future versions I may handle them as an array if there is demand.
The code
The same module contains both the Automatic documentation code and the serializer. The whole thing is kicked off with a call to
Public Function jsonStringify(o As Variant, Optional pretty As Boolean = True, _ Optional explodeArrays As Boolean = True, _ Optional explodeCollections As Boolean = False) As String
Including in your own workbook
You’ll need a number of the classes and modules in cDataSet.xlsm. Here’s how to bring in the classSerializer and associated libraries into your own workbook. Continue learning about Json and excel