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.

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

{ “projects”:[ { “project”:{ “name”:”VBAProject”, “modules”:[ { “module”:{ “name”:”cVBAProcedure”, “kind”:”ClassModule”, “procedures”:[ { “procedure”:{ “name”:”name”, “scope”:”Public”, “kind”:”Get”, “returns”:”String”, “lineCount”:3, “declaration”:”Public Property Get name() As String” } }, { “procedure”:{ “name”:”parent”, “scope”:”Public”, “kind”:”Get”, “returns”:”cVBAmodule”, “lineCount”:3, “declaration”:”Public Property Get parent() As cVBAmodule” } }, { “procedure”:{ “name”:”procKind”, “scope”:”Public”, “kind”:”Get”, “returns”:”vbext_prockind”, “lineCount”:3, “declaration”:”Public Property Get procKind() As vbext_prockind” } }, { “procedure”:{ “name”:”init”, “scope”:”Public”, “kind”:”Function”, “returns”:”cVBAProcedure”, “lineCount”:7, “declaration”:”Public Function init(m As cVBAmodule, pn As String, pk As vbext_prockind) As cVBAProcedure” } }, { “procedure”:{ “name”:”tearDown”, “scope”:”Public”, “kind”:”Sub”, “returns”:”void”, “lineCount”:3, “declaration”:”Public Sub tearDown()” } }, { “procedure”:{ “name”:”lineCount”, “scope”:”Public”, “kind”:”Get”, “returns”:”Long”, “lineCount”:3, “declaration”:”Public Property Get lineCount() As Long” } }, { “procedure”:{ “name”:”codeModule”, “scope”:”Public”, “kind”:”Get”, “returns”:”codeModule”, “lineCount”:3, “declaration”:”Public Property Get codeModule() As codeModule” } }, { “procedure”:{ “name”:”startLine”, “scope”:”Public”, “kind”:”Get”, “returns”:”Long”, “lineCount”:3, “declaration”:”Public Property Get startLine() As Long” } }, { “procedure”:{ “name”:”removeComments”, “scope”:”Private”, “kind”:”Function”, “returns”:”String”, “lineCount”:6, “declaration”:”Private Function removeComments(s As String) As String” } }, { “procedure”:{ “name”:”scope”, “scope”:”Public”, “kind”:”Get”, “returns”:”String”, “lineCount”:9, “declaration”:”Public Property Get scope() As String” } }, { “procedure”:{ “name”:”procTextKind”, “scope”:”Public”, “kind”:”Get”, “returns”:”String”, “lineCount”:27, “declaration”:”Public Property Get procTextKind() As String” } }, { “procedure”:{ “name”:”procReturns”, “scope”:”Public”, “kind”:”Get”, “returns”:”String”, “lineCount”:24, “declaration”:”Public Property Get procReturns() As String” } }, { “procedure”:{ “name”:”declaration”, “scope”:”Public”, “kind”:”Get”, “returns”:”String”, “lineCount”:22, “declaration”:”Public Property Get declaration() As String” } } ] } } ] } } ] }

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