VBA Code Documenting Tools: Project Analyzer and Visustin

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!

projanal01.gif

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:

projanal02.gif

Then I tried the graphical version of the call tree:

projanal03.gif

(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:

projanal04.gif

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

Posted in Uncategorized

15 thoughts on “VBA Code Documenting Tools: Project Analyzer and Visustin

  1. Yes, I use Aivosto project analyzer too. The motivation was a bug from a variable scope problem (not as simple as Option Explicit which *of course* I always use).

    But I must confess that I must have Dutch ancestry (my wife’s birth name was Fleming, so maybe there’s some influence there!) as I have not paid them at all. I use the demo version – full function but limited to 10 files – and make sure that my projects have 10 modules or less. When I get a more complex project, though, I’ll buy it as it IS good and as they price it in dollars which are worth so little now, it may be sooner rather than later.

    But do you really think Visustin is useful? For any nontrivial piece of work the diagrams can scarcely be readable.

  2. J K,

    “Aivosto granted me a time-limited full version of the Pro version of both tools…”

    Next time make sure You got a life-time full version as this product review by a MVP and on the largest Excl blogg is worth much more then so. An unbiased review is the best marketing channel.

    I did test it for some years ago but I didn’t followed it up because at that time I didn’t justified the investment. Now I got my own ‘in house’ toolbox (excluding the flow chart tool).

    Anyway, does it generate reports in XML file format?

    Kind regards,
    Dennis

  3. Patrick,

    I agree the diagrams maybecome unwieldy whith large routines, especially if they have a lot of branched code. But sometimes these prove very useful to detect flaws in your logic. A printer capable of large paper sizes is useful then however.

    Dennis: -whispering- of course I asked them an NFR license. They refused… Dunno about the XML.

  4. J K,

    Thanks for letting me (us?) know. As for the XML they should consider it.

    When I discovered that we in the ‘NET World’ can create XML comments which then can be compiled into help files I changed my opion about XML.

    Thanks and with kind regards,
    Dennis

  5. Well, the entreprise edition has a Project metrics report which can be exportd to XML but as far as I can tell that is it. It does seem to handle .NET XML commenting (whatever that may be).

  6. The last thing I want is a tool that points out just exactly how poor my code really is. I am probably much happier with my deluded opinions. It is kind of like those dating services that promise to match you with someone compatible. What if they keep matching you with morons? What does that say about you?

    All that being said I will download a trial copy of the software. Perhaps a second opinion is just what my code needs…

  7. wow, nice review jk…

    I think that comments really are critical. An approach that I use is to insert a stub for all methods or functions, something like this:

    ‘———————————
    ‘ Comments:

    ‘ —
    ‘———————————
    Sub ()
    ‘ ToDo: Insert Comments
    ‘ ToDo: Implement Code
    ‘ ToDo: Test Code
    End Sub

    I have versions for Functions, Properties, etc. and I keep the above in a notepad and then copy-paste whenever I need it.

    The point is that the “ToDo” notes to make sure that I (a) insert comments, (b) implement the code, and then (c) test it. (Note that I always do the comments first. :-))

    To be sure that you haven’t forgotten to do anything, just do a text search of the project for “ToDo” to see what you might have forgotten, and only remove it once the task is complete.

    Anyway, in VBA/VB6 MZTools told me that my code was 56% comments… I’ve not analyzed my code since switching over to .NET, but I suspect that it’s much, much higher…

    Regardless of one’s commenting style, I think that this and Visustin program really looks excellent.

  8. As for the Problem Report: seems quite useful. But shouldn’t at least some of the checks have been present in the compiler/interpreter?

    The first time I came across a flowchart tool is over 40 years ago; Flowbol. It won’t surprise you it analyzed Cobol programs. It printed them on a line printer using “-” and “|” trying to create boxes. Primitive!
    My problem with it was that it didn’t add any value; it showed exactly the code written, just in another “language”.
    Do you feel there is any added value in these modern tools? To me it seems there is in a thing like Problem Report, but graphing a badly structured program (I’m not talking about yours!) doesn’t seem to help in understanding the code.

    BTW, I’m not at all in favor of drawing flowcharts before starting to code. Again, it’s just another language, doesn’t add anything. Just a management hobby; you can PowerPoint it to impress/silence the principal.

    Kind regards,

    Niek Otten

  9. Mike: The ToDo’s are a nice idea to add indeed. I tend to write three question marks on any position I want to remind myself I have to do something. Your template set of three ToDo’s is good though, I just might add that to my MX-Tools template too!

    Niek: I get your point on the flowcharting. It does help sometimes though, if a routine gets complex sometimes a flowchart shows pain points. As does a call tree. Of course my motivation to produce them was a customer request…

  10. I’m with Niek on the whole flow chart thing. It has always felt to me like something for consultants to do to give the impression of value when the real purpose is to pad the bill. If your procedure is so compicated that you need a flow chart to understand it then you should consider re-writing it. Good code is simple. Great code is deceptively simple. Flow charts have their place when abstarcting very large system into their component parts but for individual procedure they are overkill.

    The biggest thing that you can do to make your code maintainable is to add commenting. Most of the code I read is IMO not adequately commented. Especially when the code is doing something which is not intuitive to work around some kind of odd circumstance or weird little nuiance.

  11. For VBA flow-charting may well be overkill, however, I write a lot of code for test automation and flow charts in that environment are invaluable especially when someone else has to pick up my work or I have to add a feature a year down the road. Having that graphic representation of what is being done with the logic really helps to make the code readable.

  12. Great Topic. 100% relevant.
    I disagree on the flow chart comments. I think it would be quick to visualise although i doubt I would use it other than to review other peoples code.
    Any Sub should only really have a basic flow (1 page of flow diagram). If it requires 4 pages to print a flow diagram then the sub should be broken down into smaller parts. I always maintain that 1 page of VBA code is the maximum size for a sub.

    I did a calc on characters of VBA code in one of my big projects in Excel and estimated the amount of courier font size 10 that would fit on a line of A4. I calculated that if the code was printed end to end at this size it would cover 1 mile (1.7 km). What’s the worst VBA monster that you have ever coded?

  13. Jan: No idea really. Name Manager is by far the most heavily edited project I’ve ever worked on. And one of the most complex I have (mostly due to the zillion workarounds it contains).

  14. More and more I working on projects which involve migrating an Excel/Access/VBA solution to a production environment such as a VB6/.Net GUI with SQL Server back-end. A recent example involved 40 KLOC across a series of workbooks and mdb files which had to be reverse engineered before migration could take place.

    Having a high-level flow chart to visualise code and logic flow would be very helpful. I also looked at Project Analyser but was dissuaded by the price considering it required the purchase of an extra module to work with VBA.

    Someone out there must own it – have you found it worth the purchase? (assuming you purchased it your self and not your employer).

  15. I tried the trial version of Project Analyzer on some code I knew had a problem that I haven’t been able to find the problem(Else without If).

    I analized the vba code and it gave me a buch of comments but no mention of a missing if or an extra else.

Leave a Reply

Your email address will not be published. Required fields are marked *