Here’s a way to automatically publish documentation about every VBA project, module, procedure and class in the currently open Workbook. There are four options for output, each directly from Excel.
- Google interactive table
- Static html web page
- A Google Apps Script Skeleton
Creating these are very similar until the final formatting, so I’ll deal with them all together. To do this, you need to ensure that the code has access to the VBE object mode, since it has to read all the code in your project to analyze and report on it. To see how to do this, see How to update modules automatically in VBA. The code can be downloaded from cDataSet.xlsm and is in the classSerializer module.
Example of Google Interactive table
Here is data on every procedure and class in cDataSet, the web page generated directly out of Excel
Example of Static version
Here is data on selected procedures in cDataSet, the web page generated directly out of Excel, this time as a static web page
Example of jSon version
How does it work ?
I’m using various classes and modules already covered on this site, in particular How to use cJobject and Regular Expressions as well the classes used in How to update modules automatically in VBA. Here’s the module classSerializer from cDataSet.xlsm (if you want to know how to include live source in your blog like the following examples, see Step by Step Gas Publisher).You can optionally select the type of output , the list of modules and the name of the html file to create as follows. Default with no arguments will list all modules in Google Table format.
Public Sub docProjectsExecute(Optional outputType As String = "google", _ Optional moduleList As Variant = Empty, _ Optional fn As String = vbNullString)
First step is to create a structure of all the projects and modules in the workbook. This uses an abstraction of the vbComponent structure in the VB IDE, namely, these classes for procedures, modules and projects.There are a couple of interesting problems to solve, for example removing comments and blank lines from the declaration, figuring out the return type of the property or the function, and dealing with the continuation lines – none of which the built in VBComponent classes help much with. Here’s how to tackle a few of those get the declaration lineremove comments
figure out the return type of a procedure
Load to cJobject structure
In another thread, I’m working on being able to serialize VBA classes. This structuring of code is a precursor to that, and the cJobject class is a key component in achieving this class serialization, so once I have arranged all the procedures in all the projects in the workbook, the next step is to load the whole thing into a cJobject as follows. This looks complex but it’s really just some simple busy work. The cJobject is just a way of creating a json.stringifiable object within the constrainst of VBA syntax Once done creating either of the html formats is just a bit of text wrangling of the cJobject. Here’s the static html example.
And of course here is the documentation for the main functions required, created by itself.
Since we now have a cJobject, the jSon output option is just a one liner
Case "json" content = job.serialize(True)
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 codeDocumenter and associated libraries into your own workbook.