Every VBA developer (which is anyone who knows how to get into the VBE I guess) develops his or her own programming habits: sparse commenting or elaborate commenting, naming convention (or no declaration at all), code indenting, preference for certain structures and methods over others. You name it. Many books have been written about this, of which I find “Code Complete” is a very good one.
Although I think I write pretty readable code, I do have one bad habit: I don’t really document what I have done and I tend to “forget” to write comments.
Sometimes a customer wants elaborate documentation of the code. But of course they forgot to ask up front…
So here I am looking at this 10.000 lines-of-code VBA project and a request to produce tech documents on what the code does and how it is structured. Including flow diagrams (preferably in Visio), call trees, the works.
At first I estimated I’d need as much as maybe half the amount of time I originally used up to write the code itself. Which was significant of course. Let’s say over a full week.
Like with any task that I find tedious: I look for a way to automate, so I dive into a search quest with Google.
Typical search strings: VBA code documenting, Document Code, Create Dependency tree, Show call stack,….
Well, I found this site.
Both their Project analyzer tool and Visustin looked like they might be a solution to my problem. But they’re both rather expensive (I’m Dutch, remember?). I calculated I’d have to invest about 1000 to cover for these two tools in the versions I think I’d need.
Luckily Aivosto granted me a time-limited full version of the Pro version of both tools so I could thoroughly test them (and to be frank I also promised to write up my experiences).
Here they are then.
I opened Project Analyzer and since I also installed the Office VBA plug, the File menu shows “Analyse Office VBA…” as one of the options. You point it to your file and it happily imports the entire VBA Project (if you have “Allow access to VBA project” set, of course). So far so good!
Take a look at the Report Menu. It shows a myriad of reporting tools. Very impressive!
I pick the “Problem Report” and it shows me lots of useful (and maybe even embarrassing) stuff, indicating line numbers and of course the routines and module, like:
Too many parameters: WriteName2sheet
194 Consider short circuit with nested Ifs
432 Unicode function is faster: ChrW$
Function without type specification
692 Too many uncommented lines: 81 (ouch)
Dead procedure
And lots of other useful stuff. Didn’t know I produced such a load of rubbish :-).
OK, let’s try something else:Procedure call tree. Wow. Everything’s there:
Then I tried the graphical version of the call tree:
(Yes I blurred this one on purpose).
So far so good. I won’t bother you all with the dozens of other reports I tried and used. I got more impressed every minute I can tell you!
…Lots of copying and pasting from Project Analyzer to Word followed…
Now let’s have a look at Visustin. Ever needed to create a flow diagram? Well I haven’t, because I tend to just dive in (I know, bad habit…).
Have a look at this procedure:
Option Explicit
Sub GetFilesInDirectory(ByVal sDirToSearch As String, colFoundFiles As Collection)
‘————————————————————————-
‘ Procedure : GetFilesInDirectory Created by Jan Karel Pieterse
‘ Company : JKP Application Development Services (c) 2006
‘ Author : Jan Karel Pieterse
‘ Created : 04-10-2007
‘ Purpose : Retrieves all files in sDirToSearch, stacks matches into cLookForFIles
‘————————————————————————-
Dim NextFile As String
Dim lCount As Long
Dim sFileName As String
Dim sFileSpec As String
Dim lFoundMatches As Long
Dim oCtlNew As CommandBarButton
Application.EnableCancelKey = xlErrorHandler
If Right(sDirToSearch, 1) <> “” Then
sDirToSearch = sDirToSearch & “”
End If
NextFile = Dir(sDirToSearch & “*.xls”)
Do Until NextFile = “”
If Err.Number = 0 Then
If TypeName(oObj2Add2) Like “Command*” Then
Set oCtlNew = oObj2Add2.Controls.Add(msoControlButton, , , , True)
oCtlNew.Caption = NextFile
oCtlNew.OnAction = “OpenFileFromMenu”
oCtlNew.Tag = sDirToSearch & NextFile
Else
AddFile2Wizard oObj2Add2, NextFile, sDirToSearch
End If
End If
NextFile = Dir()
Loop
On Error GoTo 0
TidyUp:
Exit Sub
End Sub
So now what? Well, copy, paste and hit F5. You get this:
WOW! (also proves commenting is useful…)
So next I found myself in the process of alt-tab to the VBE, select code, control-c, alt-tab to Word, paste code, alt-tab to Visustin, control-v, F5 (builds this chart), control-c, alt-tab back to Word, paste the diagram, …..
And the fun part was creating the Visio diagrams. They didn’t turn off screenupdating and I can tell you it is great fun seeing this program spitting out these (for me) complex diagrams in seconds, which would have taken me hours and hours…
All in all, producing the entire document set took me about half a day. Man, this tool cost me money! (but I gained quite a happy customer).
And to think that the enterprise version of Project Analyzer comes with macros…
Tell me what you think and what your experiences are! Have you got similar experiences, or completely different,…
Share them here!
Regards,
Jan Karel Pieterse
www.jkp-ads.com
