Through involvement in several VBA oriented mailing lists, I've observed that
a great deal of time is spent by members trying to see what has happened to
variable values, etc. in their code. And most of their questions wouldn't be
asked if they had been instructed on how to use the built in debugging tools
available in the Visual Basic Editor (VBE).
To get started, let's set some ground rules about the first things we SHOULDN'T
do to check our code:
- DON 'T set watch statements. While I don't expect anyone to
know what a watch statement is, I can tell you they can be complicated,
capable of introducing new bugs and not that informative unless you understand
how to create a watch statement. They are not the first choice
in your debugging toolbox.
- Resist the urge to check your work with a Message Box (MsgBox).
They can be helpful and seem to be everyone's first choice. But once you decide
you're going to write classes and compiled objects, this technique will raise
some real issues. To give an idea of the possibilities, think about what would
happen if your new DLL attempted to raise a MsgBox that you forgot to remove…but
it doesn't have a window in which to present the message. That's
right; the code stops executing and an invisible message is out there
someplace wishing someone would click its OK button. Ain't happening,
friends, but the problem will get you some interesting bug reports (and
such a flaw is a complete bear to identify)!
That's all for the rules so this should be pretty simple, eh?
Now let's turn our attention to the VBE and set our environment up for some
solid, basic debugging techniques!
The Immediate Problem
I guess it is a problem because, well, no one knows what it means. What's
the Immediate window about? If you open the VBE and go to the View menu to
select Immediate Window, you get this bland little window that looks like
your very first VB project. I mean, look at it!
What the? Absolutely nothing about this window is notable. That's the hidden
beauty of this little guy.
Watch what happens when I step through this body of code, though (to step
through a body of code within the VBE, press F8 to execute a line of code at
Dim I as Integer
For I = 0 to 10
Debug.print "I = " & I
Hmmm, the Immediate window is pretty interesting all of a sudden. Using this
little tool, the need for using MsgBox to test a variable completely disappears…and
it's a lot less annoying. But is that all we can do with the Immediate Window?
What if we wanted to test the value of I in our code routine manually? The
Immediate window gives an immediate answer. With the code paused, simply
type a question mark (?) followed by the variable name and press enter. Voila!
The answer is displayed in the Immediate window!
The Immediate window can also accept and format results in fixed width characters
for a result set. Using Debug.Print to force the output to the Immediate
window, you can force a wide range of variable states to Debug and format
the results to present some usable information in the Immediate window.
It should be Immediately obvious then, with all this power and such an innocent
little face, how badly outclassed the MsgBox method is by the Immediate window!
True or False, How to Test Assertions
Boolean expressions don't have to be complex and most VB examples you see
in sample code are, indeed, simple. But when your code is built on the assumption
that some condition is TR UE and you get unexpected results, you'd often like
your code to stop executing if that condition is actually FALSE. Wouldn't that
be nice? There's a way to do this easily without wearing out your keyboard
as you press F8 endlessly.
Take this code block, for example:
boolAssertion = True
For I = 0 To 10
If I > 5 Then
boolAssertion = False
The basic idea here is that we want to stop execution any time boolAssertion
changes to a FALSE condition. And, for demonstration purposes, we've forced
that condition change to occur as soon as I is incremented to 6 and the Debug
statement is encountered.
Test it by pasting the code into your VBE and pressing F5 (to make the code
run). As soon as the Assert method is encountered and boolAssertion is False,
the code stops executing with the Debug statement highlighted, ready for you
to test whatever value you want to test!
Getting to Know the Locals
So now the code has stopped executing and you want a good look at the value
of all your variables. You could start sending values to the Immediate window
during your code execution or you could wait for that Debug.Assert statement
to execute and test your variables in the Immediate window as described earlier.
You could even hover your mouse over the variable in question and get its
value popped up in a little message from VBA.
OR you could go to the View menu and choose the Locals window. This window
will show you the value and condition of any and all variables whose scope
is local to the subroutine currently executing. So, using the sample code from
the last section, we could get a snapshot from the Locals window:
There are several things to note here. First, we see that all the variables
declared locally are shown along with their Type and their current values.
But do you see the elipse (…) button in the upper right corner? What does that
To find out, let's modify the code we've used so far. We'll create a new routine
that passes an argument and then modify that argument within the called subroutine:
PassedArgument = "I made this mess!"
boolAssertion = True
For I = 0 To 10
strArgument = strArgument & " " & I
I > 5 Then
boolAssertion = False
Now, let's run it and look at the output in the Locals window when boolAssertion
goes to a FALSE condition:
There we can see the argument that was passed and the modification of its
value as the code executed. But let's now take a look at that ellipse!
When we click on it, this window appears:
It shows us what is referred to as the Call Stack. We see the subroutine we're
currently executing but we also see the name of the routine that called it,
MakeAMessWithTheLocalsWindow. Let's choose that calling routine from the window
and see what happens to the Locals window display.
Amazing!! We now see the value of the local variables of the routine calling
the code where we stopped execution and we're able to see the depth of the
Call Stack, too.
Now, if you're having trouble imagining how useful that is, consider what
happens when you engage in recursive processing. An excellent example of recursive
processing is walking a file system's directory structure. Ever wonder how
deep a process call stack could go? If not, someday you will. Here's an example
using the FileSystemObject to walk a partition's folder structure:
Dim fso As Object
Debug.Print “Starting Tree Parsing
at “ & strStartPath
ParseFolders fso, strStartPath
Tree Parsing complete.”
Sub ParseFolders(objFSO, strPath)
Dim ThisFolder, ThisFolderSubs
objFolder in ThisFolderSubs
ParseFolders objFSO, objFolder.Path
Stepping through this code for 3 or 4 cycles and we quickly lose count of
how deep we are in the recursion. Click on the Ellipse in the Local window
and you'll see just how deep you've gone:
If you click on any one of those instances in the Call Stack, the Locals window
will reveal the values of the local variables at that level of the call stack:
Togetherness and Debugging
You might also have noticed that the last example had a few of those Debug.Print
statements loitering around in the code. Well, why not? The Debug.Print method
costs nothing to use, keeps track of where we are in a very easy manner and
it NEVER interrupts the user. The results are pretty useful, too. Check out
the output we pumped to the Immediate window:
Starting Tree Parsing at C:\
We can see that whenever we reach the bottom of a particular directory tree,
the code resumes with the next lowest path in the tree that hasn't yet been
parsed. That's darned handy info!
Using all these methods together meets the first requirement of debugging
rules (the one I didn't tell you until now): debugging structures should never
cause the code to become less stable. If your sense of beauty insists the code
be pretty instead of solid, you're bending to the wrong desire. Stability should
always beat beauty in your programming and using these methods, while not the
most glamorous, goes a long way toward building solid, reliable code that doesn't
annoy your customers!
It's very easy to get carried away with defensive error and bug handling.
However, if you make good use of the built in debugging tools of the VBE,
your defensive code bodies can safely shrink as you learn more about how your
programs are behaving and what sorts of conditions arise as they execute. So
use the Immediate window to get Immediate feedback on variables, the Debug.Print
method to post variable values to the Immediate window and Debug.Assert to
stop code execution based on a FALSE condition. Get deeper understanding of
your subroutine interactions, variable scope and the Call Stack using the Locals
window. Your VBA debugging efforts will be much less troublesome as a result!