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

Public Property Get declaration() As String
' return the declaration line, stripped of comments and consolidated onto one line
Dim n As Long, s As String, t As String, doMore As Boolean

n = 0
t = vbNullString
Do
s = LTrim(RTrim(removeComments(codeModule.Lines(n + startLine, 1))))
If (s <> vbNullString) Then
doMore = Right(s, 1) = "_" And n < lineCount
If (doMore) Then s = left(s, Len(s) - 1)
t = t & s
Else
doMore = False
End If
n = n + 1

Loop While doMore
declaration = t

End Property

remove comments

Private Function removeComments(s As String) As String
    Dim sr As String
    ' regex for comments (accounts for within quoted strings)
    sr = bracket(q & ".*?" & q) & "|" & bracket("'.*$") ' (".*?")|('.*$)
    removeComments = rxReplace(sr, s, "$1")
End Function

figure out the return type of a procedure


Public Property Get procReturns() As String
    ' return the type of thing it returns
    Dim dec As String, r As String
    'regex to get as xxx
    r = "(.*[^\w+$])(\w+$)"
    Select Case procTextKind
    
        Case "Get", "Function"
            dec = declaration
            
            If (rxTest(r, dec)) Then
                procReturns = rxReplace(r, dec, "$2")
            Else
                procReturns = "Variant"
            End If
            
        Case "Set", "Let", "Sub"
            procReturns = "void"
            
        Case Else
            Debug.Assert False
        
    End Select
End Property

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
 

​Public Function projectsToJobject(Optional moduleList As Variant = Empty) As cJobject
    
    Dim job As cJobject, projects As Collection, project As cVBAProject, fn As String, _
        module As cVBAmodule, procedure As cVBAProcedure, argument As cVBAArgument
        
    ' get everything in all projects
    Set projects = getClasses
    
    ' everything will be dumped here
    Set job = New cJobject
    
    With job.init(Nothing).add("projects").addArray
        For Each project In projects
            With .add.add("project")
                .add "name", project.name
                If requiredModuleCount(project, moduleList) > 0 Then
                    With .add("modules").addArray
                        For Each module In project.modules
                            If (requiredModule(module, moduleList)) Then
                                With .add.add("module")
                                    .add "name", module.name
                                    .add "kind", module.textKind
                                    With .add("procedures").addArray
                                        For Each procedure In module.procedures
                                            With .add.add("procedure")
                                                .add "name", procedure.name
                                                .add "scope", procedure.scope
                                                .add "kind", procedure.procTextKind
                                                .add "returns", procedure.procReturns
                                                .add "lineCount", procedure.lineCount
                                                .add "declaration", procedure.declaration
                                                With .add("arguments").addArray
                                                    For Each argument In procedure.arguments
                                                        With .add.add("argument")
                                                            .add "name", argument.name
                                                            .add "optional", argument.isOptional
                                                            .add "default", argument.default
                                                            .add "argtype", argument.argType
                                                        End With
                                                    Next argument
                                                End With
                                            End With
                                        Next procedure
                                    End With
                                End With
                            End If
                        Next module
                    End With
                End If
            End With
        Next project
    End With
    tearDownProjects projects
    Set projects = Nothing
   Set projectsToJobject = job
End Function

Once done creating either of the html formats is just a bit of text wrangling of the cJobject. Here’s the static html example.

Private Function projectJobToStaticHtml(job As cJobject) As String
    Dim jProject As cJobject, jModule As cJobject, jProcedure As cJobject, s As String, h As String, _
        t As cStringChunker, theHeader As String, theBody As String, theTable As cStringChunker, _
        jargument As cJobject, stripe As Variant
    stripe = Array("odd", "even")

    Set t = New cStringChunker
    Set theTable = New cStringChunker
    
    ' create a row for each procedure
    For Each jProject In job.child("projects").children
        If (jProject.child("project").childExists("modules") Is Nothing) Then
            MsgBox ("there is nothing to do - no matchin modules")
            Exit Function
        Else
            For Each jModule In jProject.child("project.modules").children
                For Each jProcedure In jModule.child("module.procedures").children
                    With jProcedure.child("procedure")
                        ' the <a> id is used to bring detail on mouseover
                        t.add encloseTag("TR", , CStr(stripe(LBound(stripe) + .childIndex Mod 2)), _
                            encloseTag("TD", False, , Array(jProject.toString("project.name"), _
                                jModule.toString("module.name"), _
                                jModule.toString("module.kind"), _
                                "<a id='a_" & Replace(jProcedure.fullKey, ".", "_") & _
                                "' href='#' class='viewdiv'>" & _
                                .toString("name") & "</a>", .toString("scope"), _
                                .toString("kind"), .toString("returns"), .toString("lineCount"), _
                                .toString("declaration"))))
                    End With
                Next jProcedure
            Next jModule
        End If
    Next jProject
    ' the table header
    h = encloseTag("THEAD", , , encloseTag("TR", , , _
        encloseTag("TH", False, , Array("Project", "Module", "Type", "Procedure", _
            "Scope", "Kind", "Returns", "Line count", "Call"))))
    ' tag the table
    theTable.add encloseTag("TABLE", , , h + encloseTag("TBODY", , , t.content))
    
    ' now each procedure.argument
    
    For Each jProject In job.child("projects").children
        For Each jModule In jProject.child("project.modules").children
            For Each jProcedure In jModule.child("module.procedures").children
                With jProcedure.child("procedure")
                    t.clear
                    For Each jargument In .child("arguments").children
                        With jargument
                            t.add encloseTag("TR", , CStr(stripe(LBound(stripe) + .childIndex Mod 2)), _
                                encloseTag("TD", False, , Array( _
                                .toString("argument.name"), _
                                .toString("argument.argType"), _
                                .toString("argument.optional"), _
                                .toString("argument.default"))))
                        End With
                    Next jargument
                    
                    h = encloseTag("THEAD", , , encloseTag("TR", , , _
                        encloseTag("TH", False, , _
                        Array("Name", "Type", "Optional", "Default"))))
                    
                    ' tag the table, the ID is used for interactivity
                    theTable _
                        .add("<div id='da_" & Replace(jProcedure.fullKey, ".", "_") _
                                & "' class='hide'>") _
                        .add(encloseTag("div", , "even", _
                            .toString("kind") & " " & _
                            jModule.toString("module.name") & "." & .toString("name") & _
                            "() returns " & .toString("returns"))) _
                        .add(encloseTag("TABLE", , , h & encloseTag("TBODY", , , t.content))) _
                        .add ("</div>")
                        
                    
                End With
            Next jProcedure

        Next jModule
    Next jProject

    With t.clear
        .add encloseTag("STYLE", , , tableStyle() + basicStyle())
        .add includeJQuery & includeGoogleCallBack(jDivAtMouse)
        theHeader = encloseTag("HEAD", , , .content)
    End With

    theBody = encloseTag("BODY", , , scrollHack & theTable.content & "</div>")
    
    ' now we have the table, write it to an html file
    projectJobToStaticHtml = "<!Doctype html>" & encloseTag("html", , , theHeader + theBody)

End Function



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.