Automatic documentation

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.

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.

Page Content hide
1 Example of Google Interactive table
2 Example of Static version
3 Example of jSon version
4 How does it work ?
5 Walkthrough
6 Load to cJobject structure
7 Documentation
8 JSON creation
9 Including in your own workbook
10 Share with your network

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

Here is data on selected procedures in cDataSet, the web page generated directly out of Excel, this time as a json data, for further javascript processing

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)

Walkthrough

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.

Documentation

And of course here is the documentation for the main functions required, created by itself. 

JSON creation

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. 
Subpage

Step by step module updating