Debugging VBA Code

Problem

You want to know how to debug your code in VBA.

Solution

Use VBA's built-in debugger by accessing the Debug menu in the VBA IDE or working with the Debug toolbar (select View images/U2192.jpg border=0> Toolbars images/U2192.jpg border=0> Debug to show the Debug toolbar).

Discussion

When you are writing code, VBA will automatically check for syntax errors as soon as you press Enter following each code line. If VBA finds no syntax errors, your code will be accepted and it will be highlighted to denote comments and keywords. If VBA finds an error, it will immediately display an error message box with information on the error. It will also highlight your code statement and indicate which part is causing the error. You can then edit the statement in the code window until you get it right.

Alas, VBA cannot automatically detect logical errors in your code. That technology is a long way off for any computing language. In the meantime, we do have integrated debuggers to help us find and correct logical errors. Logical errors are the sort of error where your code, though syntactically correct, does not do what you intend. For example, you might have written an integration routine based on the Improved Euler method and it does not appear to work correctly; e.g., it's giving you garbage results. Such poor results could be due to a time step that's too small, an incorrect formula in your code, or a particularly nasty function that you're trying to integrate.

For tracking down and fixing logical errors, the debugger included with the VBA IDE is an invaluable tool. Let's say you're trying to debug the procedure shown in Figure 2-4.

Figure 2-4. Debugging a procedure

The first thing you should do is display the Debug toolbar by selecting View images/U2192.jpg border=0> Toolbars images/U2192.jpg border=0> Debug from the main menu bar. The Debug toolbar is shown in Figure 2-4 in the upper-right side of the window. Figure 2-5 shows a close-up of the Debug toolbar.

Figure 2-5. Debug toolbar

The next thing you need to do is set some breakpoints in the procedure you're trying to debug. Breakpoints are lines in your code that you tag to let the debugger know you want to stop at that line while debugging, in order to inspect what's going on during execution. To set a breakpoint, simply click in the margin to the left of the code window adjacent to any executable line upon which you'd like to break. The breakpoints will be highlighted as in Figure 2-4. The two breakpoints shown there have a small circle in the margin and a dark rectangle highlighting the code line where the debugger will break.

Now you can start the debugger and inspect what's happening in your code. Place the cursor in the procedure you want to debug and press the Continue button on the toolbar (see Figure 2-5) to start the debugger. The debugger will execute your procedure and break when it encounters a breakpoint.

Once a breakpoint has been reached, you can take several actions to inspect your code and follow its execution. One thing you might do is step line by line through your code to follow the execution path. To step through your code, you can press the Step Into, Step Over, or Step Out button on the Debug toolbar (see Figure 2-5). Step Over will execute the current code line and stop on the next line. The current line, the one upon which execution has paused, is also highlighted with an arrow icon in the margin (see Figure 2-4). Step Into is useful if you want to follow code execution line by line into other procedures that may be called from within the one you're trying to debug. Step Out is useful for jumping out of a procedure and getting back to the calling procedure to continue line by line stepping.

While stepping through your code, it's helpful to actually inspect the values of variables used in your algorithms. This is how you can check to see if things are being calculated correctly or where things may be going wrong. The easiest way to inspect a value is to hold the mouse cursor over a variable name and wait a second or so for VBA to display a small rectangle showing the current value of the selected variable.

You may also display the Locals window to show all local variables within the procedure you're debugging. Press the Locals button on the Debug toolbar (see Figure 2-5) to open the Locals window. The Locals window will show not only the variables, but their values as well. This is very handy when stepping through to monitor changes in values of some key variables.

Alternatively, you can open the Watch window (use the toolbar button shown in Figure 2-5) to monitor select variables. Initially the Watch window will be empty. To add a variable to watch, highlight or place the cursor on the desired variable in the code window, and then right-click to reveal a pop-up menu. Select Add Watch to add the variable to the Watch window.

With either of these windows open, you can continue to step through your code, monitoring how key variables change. Watching variables this way and in the context of executing statements might illuminate problems in your code.

VBA also includes a window called the Immediate window . This window can be used for typing in commands or writing output using the Print statement. When you are debugging, the Immediate window is useful for writing debugging text so you can trace the execution of the code or follow the value of a variable as the procedure executes. To display the Immediate window, press the toolbar button shown in Figure 2-5. You can send output to the Immediate window using the Print method. When debugging, you have to use the Debug object's Print method, as in Debug.Print Str(k1(1)). In this example, the value stored in element 1 of the k1 array is converted to a string, using the Str function, and then printed to the Immediate window.

I use the Immediate window in this way for long procedures or complex programs that are simply too long or complicated to step through line by line. I strategically place print statements in the code that will send write messages informing me of the status of certain things of interest. Such things may include variable values or whether or not a particular line or block of code was executed.

I often use the Beep statement to play a short tone during execution of a procedure. I'll place it strategically if I want to determine whether a particular block of code is reached. For quick checks, using Beep is faster than typing in a message to display via the Print method.

At any point during debugging, you can press the Continue button to continue code execution from the current line. Execution will proceed until another breakpoint is encountered or the end of the procedure is reached. You can also terminate a debugging session by pressing the Reset button (see Figure 2-5).