Bug Extermination Techniques in Excel VBA

In This Chapter

► Defining a bug and why you should squash it
► Recognizing types of program bugs you may encounter
► Using techniques for debugging your code Using the VBA built-in debugging tools
In the word bugs conjures up an image of a cartoon rabbit, this chapter can set you straight. Simply put, a bug is an error in your programming. Here I cover the topic of programming bugs — how to identify them and how to wipe them off the face of your module.

Species of Bugs

Welcome to Entomology 101. The term program bug, as you probably know, refers to a problem with software. In other words, if software doesn’t perform as expected, it has a bug. Fact is, all major software has bugs — lots of bugs. It has been said that software that doesn’t contain bugs is probably so trivial that it’s not worth using. Excel itself has hundreds (if not thousands) of bugs. Fortunately, the vast majority of these bugs are relatively obscure and appear in only very unusual circumstances.
When you write VBA programs, your code probably will have bugs. This is a fact of life and not necessarily a reflection of your programming ability. The bugs may fall into any of the following categories:
Logic flaws in your code. You can often avoid these bugs by carefully thinking through the problem your program addresses.
Incorrect context bugs. This type of bug surfaces when you attempt to do something at the wrong time. For example, you may try to write data to cells in the active sheet when the active sheet is not a worksheet.
Extreme-case bugs. These bugs rear their ugly heads when you encounter data you didn’t anticipate, such as very large or very small numbers.
Wrong data type bugs. This type of bug occurs when you try to process data of the wrong type, such as attempting to take the square root of a text string.
Wrong version bugs. This type of bug involves incompatibilities between different Excel versions. For example, you may develop a workbook using Excel 2003 and then find out that the workbook doesn’t work with Excel 95 or 97. You can usually avoid such problems by avoiding version-specific features. Often, the easiest approach is to develop your application using the lowest version number of Excel that users might have.
Beyond-your-control bugs. These are the most frustrating. An example occurs when Microsoft upgrades Excel and makes a minor, undocumented change that causes your macro to bomb.
Debugging is the process of identifying and correcting bugs in your program. Developing debugging skills takes time, so don’t be discouraged if this process is difficult at first.
It’s important to understand the distinction between bugs and syntax errors. A syntax error is a language error. For example, you might misspell a keyword, omit the Next statement in a For-Next loop, or have a mismatched parenthesis. Before you can even execute the procedure, you must correct these syntax errors. A program bug is much subtler. You can execute the routine, but it doesn’t perform as expected.


Identifying Bugs

Before you can do any debugging, you must determine whether a bug actually exists. You can tell that your macro contains a bug if it doesn’t work the way it should. (Gee, this topic is just filled with insight, isn’t it?) Usually, but not always, you can easily discern this.
A bug often (but not always) becomes apparent when Excel displays a runtime error message. Figure 13-1 shows an example. Notice that this error message includes a button labeled Debug. More about this later in the “About the Debugger” section.
It’s important to remember that bugs often appear when you least expect them. For example, just because your macro works fine with one data set doesn’t mean you can assume it will work equally as well with all data sets. The best debugging approach is thorough testing, under a variety of real-life conditions.
An error message like this often means that your VBA code contains a bug.
Figure 13-1:
An error message like this often means that your VBA code contains a bug.

Debugging Techniques

In this section, I discuss the four most common methods for debugging Excel VBA code:

Examining the code

Inserting MsgBox functions at various locations in your code Inserting Debug.Print statements Using the Excel built-in debugging tools

Examining your code

Perhaps the most straightforward debugging technique is simply taking a close look at your code to see whether you can find the problem. If you’re lucky, the error jumps right out and you can quickly correct it.
Notice I said “If you’re lucky.” That’s because often you discover errors when you have been working on your program for eight hours straight, it is 2:00 a.m., and you are running on caffeine and willpower. At times like that, you are lucky if you can even see your code, let alone find the bugs. Thus, don’t be surprised if examining your code alone doesn’t expunge all the bugs it contains.

Using the MsgBox function

A common problem in many programs involves one or more variables not taking on the values you expect. In such cases, monitoring the variable(s) while your code runs is a helpful debugging technique. Do this by inserting
temporary MsgBox functions in your routine. For example, if you have a variable named CellCount, you can insert the following statement:
tmp33-2_thumb
When you execute the routine, the MsgBox function displays CellCount’s value.
It’s often helpful to display the values of two or more variables in the message box. The following statement displays the current value of Looplndex and CellCount, as shown in Figure 13-2:
tmp33-3_thumbUsing a message box to display the value of two variables.
Figure 13-2:
Using a message box to display the value of two variables.
Notice that I combine the two variables with the concatenation operator (&) and insert a space character between them. Otherwise, the message box strings the two values together, making them look like a single value. You can also use the built-in constant, vbNewLine, in place of the space character. vbNewLine inserts a line-feed break, which displays the text on a new line. The following statement displays three variables, each on a separate line:
tmp33-5_thumb
This technique isn’t limited to monitoring variables. You can use a message box to display all sorts of useful information while your code is running. For example, if your code loops through a series of sheets, the following statement displays the name and type of the active sheet:
tmp33-6_thumb
I use MsgBox functions frequently when I debug my code. Just make sure that you remove them after you identify and correct the problem.

Inserting Debug.Print statements

As an alternative to using MsgBox functions in your code, you can insert one or more temporary Debug.Print statements. Use these statements to print the value of one or more variables in the Immediate window. Here’s an example that displays the value of three variables:
tmp33-7_thumb
Notice that the variables are separated with a comma. You can display as many variables as you like using a single Debug.Print statement. If VBE’s Immediate window is not visible, press Ctrl+G.
After you’ve debugged your code, make sure to remove all of the Debug.Print statements.

Using the VBA debugger

The Excel designers are intimately familiar with the concept of bugs. Consequently, Excel includes a set of debugging tools that can help you correct problems in your VBA code. The VBA debugger is the topic of the next section.

About the Debugger

In this section, I discuss the gory details of using the Excel debugging tools. These tools are much more powerful than the techniques I discuss in the previous section. But along with power comes responsibility. Using the debugging tools takes a bit of setup work.

Setting breakpoints in your code

Earlier in this chapter, I discuss using MsgBox functions in your code to monitor the values of certain variables. Displaying a message box essentially halts your code in midexecution, and clicking the OK button resumes execution.
Wouldn’t it be nice if you could halt a routine’s execution, take a look at any of your variables, and then continue execution? Well, that’s exactly what you can do by setting a breakpoint.

You can set a breakpoint in your VBA code in several ways:

Move the cursor to the statement at which you want execution to stop; then press F9.
Click in the gray margin to the left of the statement at which you want execution to stop.
Position the insertion point in the statement at which you want execution to stop. Then use the Debug Toggle Breakpoint command.
Right-click a statement and choose Toggle Break point from the shortcut menu
The results of setting a breakpoint are shown in Figure 13-3. Excel highlights the line to remind you that you set a breakpoint there and also inserts a large dot in the gray margin.
The highlighted statement marks a breakpoint in this procedure.
Figure 13-3:
The highlighted statement marks a breakpoint in this procedure.
When you execute the procedure, Excel goes into Break mode when the line with the breakpoint is executed. In Break mode, the word [break] is displayed in the VBE title bar. To get out of Break mode and continue execution, press F5 or click the Run Sub/UserForm button in the VBE toolbar. Refer to “Stepping through your code” later in this chapter to find out more.
To quickly remove a breakpoint, click the large dot in the gray margin or move the cursor to the highlighted line and press F9. To remove all breakpoints in the module, press Ctrl+Shift+F9.
What is Break mode? You can think of it as a state of suspended animation. Your VBA code stops running and the current statement is highlighted in bright yellow. In Break mode, you can
Type VBA statements in the Immediate window. (See the next section for details.)
Step through your code one line at a time to check various things while the program is paused.
In Break mode, you can move the mouse pointer over a variable to displays its value in a small pop-up window. Figure 13-4 shows an example.
In Break mode, move the mouse pointer over a variable to display its current value. In this example, the cell has a value of 43.
Figure 13-4:
In Break mode, move the mouse pointer over a variable to display its current value. In this example, the cell has a value of 43.

Using the Immediate window

The Immediate window may not be visible in the VBE. You can display the VBE’s Immediate window at any time by pressing Ctrl+G.
In Break mode, the Immediate window (see Figure 13-5) is particularly useful for finding the current value of any variable in your program. For example, if you want to know the current value of a variable named CellCount, enter the following in the Immediate window and press Enter:
tmp33-10_thumb
You can save a few milliseconds by using a question mark in place of the word Print, like this:
tmp33-11_thumbThe Immediate window in action. The cell's current value is 43.
Figure 13-5:
The Immediate window in action. The cell’s current value is 43.
The Immediate window lets you do other things besides checking variable values. For example, you can change the value of a variable, activate a different sheet, or even open a new workbook. Just make sure that the command you enter is a valid VBA statement.
You can also use the Immediate window when Excel is not in Break mode. I often use the Immediate window to test small code snippets (whatever you can cram on a single line) before incorporating them into my procedures.

Stepping through your code

While in Break mode, you can also step through your code line by line. One statement is executed for each time you press F8. Throughout this line-byline execution of your code, you can activate the Immediate window at any time to check the status of your variables.

Using the Watch window

In some cases, you may want to know whether a certain variable or expression takes on a particular value. For example, suppose that a procedure loops through 1,000 cells. You notice that a problem occurs during the 900th iteration of the loop. Well, you could insert a breakpoint in the loop, but that would mean responding to 899 prompts before the code finally gets to the iteration you want to see (and that gets boring real fast). A more efficient solution involves setting a watch expression.
For example, you can create a watch expression that puts the procedure into Break mode whenever a certain variable takes on a specific value — for example, Counter=900. To create a watch expression, choose DebugOAdd Watch to display the Add Watch dialog box. See Figure 13-6.
The Add Watch dialog box lets you specify a condition that causes a break.
Figure 13-6:
The Add Watch dialog box lets you specify a condition that causes a break.

The Add Watch dialog has three parts:

Expression: Enter a valid VBA expression or a variable here. For example, Counter=900 or just Counter.
Context: Select the procedure and the module you want to watch. Note that you can select All Procedures and All Modules.
Watch Type: Select the type of watch by clicking an option button. Your choice here depends on the expression you enter. The first choice, Watch Expression, does not cause a break; it simply displays the expression’s value when a break occurs.
Execute your procedure after setting up your watch expression(s). Things run normally until your watch expression is satisfied (based on the Watch Type you specified). When that happens, Excel enters Break mode (unless the Watch Type is set to Watch Expression). From there, you can step through the code or use the Immediate pane to debug your code.
When you create a watch, VBE displays the Watches window shown in Figure 13-7. This window displays the value of all watches that you’ve defined.
The Watches window displays all watches.
Figure 13-7:
The Watches window displays all watches.
The best way to understand how this Watch business works is to use it and try various options. Before long, you realize what a useful tool it is.

Bug Reduction Tips

I can’t tell you how to completely eliminate bugs in your programs, but I can provide a few tips to help you keep those bugs to a minimum:
Use an Option Explicit statement at the beginning of your modules.
This statement requires you to define the data type for every variable you use. This creates a bit more work for you, but you avoid the common error of misspelling a variable name. And it has a nice side benefit: Your routines run a bit faster.
Format your code with indentation. Using indentations helps delineate different code segments. If your program has several nested For-Next loops, for example, consistent indentation helps you keep track of them all.
Be careful with the On Error Resume Next statement. As I discuss in Chapter 12, this statement causes Excel to ignore any errors and continue executing the routine. In some cases, using this statement causes Excel to ignore errors that it shouldn’t ignore. Your code may have bugs and you may not even realize it.
Use lots of comments. Nothing is more frustrating than revisiting code you wrote six months ago and not having a clue as to how it works. By adding a few comments to describe your logic, you can save lots of time down the road.
Keep your Sub and Function procedures simple. By writing your code in small modules, each of which has a single, well-defined purpose, you simplify the debugging process.
Use the macro recorder to help identify properties and methods. When I can’t remember the name or the syntax of a property or method, I often simply record a macro and look at the recorded code.
Understand Excel’s debugger. Although it can be a bit daunting at first, the Excel debugger is a useful tool. Invest some time and get to know it.
Debugging code is not one of my favorite activities (it ranks right up there with getting audited by the IRS), but it’s a necessary evil that goes along with programming. As you gain more experience with VBA, you spend less time debugging and become more efficient at doing so.

Next post:

Previous post: