Bogus Compile Errors

Hi all,

Let me start this post with a shameless plug: Charles Williams and I developed Name Manager. From my website alone this tool is good for about 60 downloads a day.

Just a couple of weeks ago, all of a sudden Charles and I started receiving complaints about compile errors, which neither of us could reproduce. Because the version we had available back then used the treeview control from the Windows common controls library, we blamed that control. And indeed, removing the control from the form that housed it fixed the problem for some of our users. But not for all.

Since this kind of errors is extremely hard to troubleshoot, I thought it might be useful to share our experiences.

Luckily we found a user who was willing to help us trouble-shoot the matter. We asked him to do all sorts of things: try on a different client, try logging on as administrator, removing accounts from client, you name it.
After exchanging a host of emails and screenshots and trying all sorts of variations we discovered the culprit: problematic .EXD files in one of the system folders of the client computer (these are just an example, your system may show others too or even none at all):


These files are typically stored in this location:

C:\Documents and Settings\[UserName]\Application Data\Microsoft\Forms

So far, removing these files from that folder has resolved the issue for the people that experienced trouble with Name Manager.

So, if you distribute an addin to other users and you get a complaint about compile errors in your work: start off by asking the user to weed out the Forms folder I showed above. If it doesn’t help, it certainly wont hurt!


Jan Karel Pieterse

Catching Paste Operations

You know the situation: You have carefully setup a workbook with intricate Validation schemes. But then along comes your user and he copies and pastes at will. Result: Validation zapped, workbook structure violated.

What to do? The only way I find to be reliable is to catch all possible paste operations. But this isn’t very easy, since there are a zilion ways to paste.

I have put together a sample workbook and an explanatory article on how one could go about protecting a workbook by intercepting paste operations.

Let me know what you think!!


Jan Karel Pieterse

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!


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
                AddFile2Wizard oObj2Add2, NextFile, sDirToSearch
            End If
        End If

        NextFile = Dir()
    On Error GoTo 0
    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!


Jan Karel Pieterse

Beta testing request

Hi everyone,

I am busy building my very first COM addin for Excel and I’ve now come to the stage that I need some beta testers.

Who would be willing to run some tests on my new “Excel Formula Reference Auditing Utility” (see screenshot below)?
If interested, send me an email:

What’s in it for you? a free copy of the tool once the beta is finished.

###EDIT Oct 29, 2007###
I’d like to thank everyone who has volunteered for beta testing. For now, I have sufficient people doing testing, so the subscription is closed.


Jan Karel Pieterse

The New Excel 2007 File Format

Most of you will know that Excel 2007 (well, Office 2007) comes with a brand new file format, based on what MSFT calls Open XML.

This suddenly enables us to write code that can easily generate/change Office 2007 files without the need for an Office installation. For instance on a server.

Whilst there is proper documentation on this file format, the document with detailed descriptions of the Open XML format (“part 4? in the aforementioned link) counts an astonishing 4721 pages !!

This is why I decided to write up a couple of basic pages on how to do stuff with these Open XML files.

My first one is about reading and editing cells:

Working With Worksheet Data In An Excel 2007 File



Jan Karel Pieterse