JSON stringify VBA classes

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