Excel VBA Programming For Dummies, 4th Edition (2015)

Part III. Programming Concepts

Chapter 13. Bug Extermination Techniques

In This Chapter

Defining a bug and knowing 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

Presenting a handy list of bug reduction tips

If 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 programs have bugs — lots of bugs. Excel itself has hundreds (if not thousands) of bugs. Fortunately, the vast majority of these bugs are relatively obscure and appear in only very specific circumstances.

When you write nontrivial 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, your code may try to write data to cells in the active sheet when the active sheet is actually a chart sheet (which has no cells).

· 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 with Excel 2016 and then find out that the workbook doesn’t work with Excel 2003. You can usually avoid such problems by not using version-specific features. Often, the easiest approach is to develop your application by using the lowest version number of Excel that users might have. In all cases, however, you should test your work on all versions you expect it will be used with.

· 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. Even security updates have been known to cause problems.

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 book 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.

Figure 13-1: An error message like this often means that your VBA code contains a bug.

A key fact known to all programmers is 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 to start with thorough testing, under a variety of real-life conditions. And because any workbook changes made by your VBA code cannot be undone, it is always a good idea to use a backup copy of the workbook that you use for testing. I usually copy some files into a temporary folder and use those files for my testing.

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. This method, of course, requires knowledge and experience. In other words, you have to know what you're doing. If you’re lucky, the error jumps right out, and you slap your forehead and say, "D’oh!" When the forehead pain diminishes, you can fix the problem.

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 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 simply examining your code isn't enough to make you find and 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. One way to do this is by inserting temporary MsgBox functions into your routine. For example, if you have a variable named CellCount, you can insert the following statement:

MsgBox CellCount

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 two variables: LoopIndex (1) and CellCount (72), separated by a space.

MsgBox LoopIndex & " " & CellCount

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 (see Figure 13-2):

MsgBox LoopIndex & vbNewLine & CellCount & vbNewLine & MyVal

Figure 13-2: Using a message box to display the value of three variables.

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:

MsgBox ActiveSheet.Name & " " & TypeName(ActiveSheet)

If your message box shows something unexpected, press Ctrl+Break, and you see a dialog box that tells you Code execution has been interrupted; as shown in Figure 13-3, you have four choices:

· Click the Continue button. The code continues executing.

· Click the End button. Execution stops.

· Click the Debug button. The VBE goes into Debug mode (which I explain a bit later in the section “About the Debugger”).

· Click the Help button. A help screen tells you that you pressed Ctrl+Break. In other words, it's not very helpful.

Figure 13-3: Pressing Ctrl+Break halts execution of your code and gives you some choices.

If your keyboard doesn’t have a Break key, try pressing Ctrl+ScrollLock.

Feel free to use MsgBox functions frequently when you debug your 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 values of three variables:

Debug.Print LoopIndex, CellCount, MyVal

Notice that the variables are separated with commas. You can display as many variables as you like with a single Debug.Print statement.

Debug.Print sends output to the Immediate window even if that window is hidden. If VBE’s Immediate window is not visible, press Ctrl+G (or choose View  ⇒  Immediate Window). Figure 13-4 shows some output in the Immediate window.

Figure 13-4: A Debug.Print statement sends output to the Immediate window.

Unlike MsgBox, Debug.Print statements do not halt your code. So you need to keep an eye on the Immediate window to see what's going on.

After you've debugged your code, be sure to remove all the Debug.Print statements. Even big companies like Microsoft occasionally forget to remove their Debug.Print statements. In several previous versions of Excel, every time the Analysis ToolPak add-in was opened, you’d see several strange messages in the Immediate window. That problem was finally fixed in Excel 2007.

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 mid-execution, and clicking the OK button resumes execution.

Wouldn’t it be nice if you could halt a routine’s execution, take a look at the value of 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 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 choose the Debug  ⇒  Toggle Breakpoint command.

· Right-click a statement and choose Toggle  ⇒  Breakpoint from the shortcut menu.

The results of setting a breakpoint are shown in Figure 13-5. Excel highlights the line to remind you that you set a breakpoint there; it also inserts a large dot in the margin.

Figure 13-5: The highlighted statement marks a breakpoint in this procedure.

When you execute the procedure, Excel goes into Break mode before 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 on the VBE toolbar. See “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.

VBA also has a keyword, which you can insert as a statement, that forces Break mode:

Stop

When your code reaches the Stop keyword, VBA enters Break mode.

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.)

· Press F8 to step through your code one line at a time to check various things while the program is paused.

· Move the mouse pointer over a variable to display its value in a small pop-up window.

· Skip the next statement(s) and continue execution there (or even go back a couple of statements).

· Edit a statement and then continue.

Figure 13-6 shows some debugging action. A breakpoint is set (notice the big dot), and I'm using the F8 key to step through the code line by line (notice the arrow that points to the current statement). I use the Immediate window to check a few things, the mouse pointer is hovering over the OutputRow variable, and the VBE displays its current value.

Figure 13-6: A typical scene in Break mode.

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

Print CellCount

You can save a few milliseconds by using a question mark in place of the word Print, like this:

? CellCount

The Immediate window lets you do other things besides check 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 I 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 each time you press F8. Throughout this line-by-line execution of your code, you can activate the Immediate window at any time to check the status of your variables.

You can use your mouse to change which statement VBA will execute next. If you put your mouse pointer in the margin to the left of the currently highlighted statement (which will usually be yellow), your pointer changes to a right-pointing arrow. Simply drag your mouse to the statement you want to execute next and watch that statement turn yellow.

Using the Watch window

In some cases, you may want to know whether a certain variable or expression takes on a particular value. 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 into 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 Debug  ⇒  Add Watch to display the Add Watch dialog box (see Figure 13-7).

Figure 13-7: The Add Watch dialog box lets you specify a condition that causes a break.

The Add Watch dialog box 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 (you did set the Watch Type to Break When Value Is True, didn’t you?). From there, you can step through the code or use the Immediate window to debug your code.

When you create a watch, VBE displays the Watches window, shown in Figure 13-8. This window displays the value of all watches that you’ve defined. In this figure, the value of the Counter variable hit 900, which caused Excel to enter Break mode.

Figure 13-8: The Watches window displays all watches.

To remove a watch, right-click it in the Watches window, and choose Delete Watch from the shortcut menu.

The best way to understand how this Watch business works is to use it and try various options. Before long, you'll probably wonder how you ever got along without it.

Using the Locals window

Another useful debugging aid is the Locals window. You can show this window by choosing View  ⇒  Locals Window in the VB. When you are in Break mode, this window shows you a list of all variables that are local to the current procedure (see Figure 13-9). The nice thing about this window is that you don’t have to add a load of watches manually if you want to look at the content of many variables. The VBE has done all the hard work for you.

Figure 13-9: The Locals window displays all local variables and their content.

Bug Reduction Tips

I can’t tell you how to completely eliminate bugs in your VBA programs. Finding bugs in software can be a profession by itself, 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, when you have to debug, you are more efficient at doing so.