You can download all this in the cDataSet.xlsm workbook. Here's how it all fits together.
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.
Here's the stringify code
Using the Data Manipulation Classes, here's a selection of calls to serializing various subclasses of cDataSet
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.
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
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.
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.
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 same module contains both the Automatic documentation code and the serializer. The whole thing is kicked off with a call to
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.
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > excel to json and back >