Debugging VBA Code

This page describes methods for debugging your VBA code.

Introduction

Debugging a program is one of the most important steps in software development. Knowledge of VBA's debugging tools can make debugging easier and more productive. This page describes several of VBA's built-in debugging tools you can use when testing and debugging your application.

Stepping Through Code

One of the first methods to debug code is to step through the code one line at a time. To step through code, put the cursor on the first line of code to be analyzed and press F8 or choose Step Into on the Debug menu. The next line of code to be executed will be displayed in yellow background with a black font. Note that the highlighted line of code has not yet been executed -- it is the next line to execute.

If your code calls another procedure, stepping through the code with F8 will cause execution to enter the called procedure in a line-by-line sequence. If you want to execute the called procedure without stepping through it, press Shift+F8. This will execute the called procedure and then pause on the line of code after calling the procedure. If you are already stepping through a procedure, you can press Ctrl+ F8 to resume code execution line-by-line. At any time you are paused either in step-by-step mode or at a breakpoint (see below), you can press F5 or Continue from the Run menu to cause VBA to run to completion or until a pause statement is encountered.

Whenever you are paused in step-by-step mode, you can query or change a variable's value from the Immediate window.

Break Points And The Stop Command

A breakpoint is a marker placed on a line of code that causes execution to pause immediately before executing that line. You can add a breakpoint to a line of code by putting the cursor on the line of code in question and pressing F9, choosing Toggle Breakpoint on the Debug menu, or clicking in the left margin next to the line of code. When a breakpoint is set, the line is displayed in brick-red background with a white font. When you run the code, execution will pause immediately before the line of code with the breakpoint and will display it in yellow background with a black font. Note than the line in yellow has not yet been executed -- it is the next line of code to run.

While the code is paused at the breakpoint, you can issue commands in the Immediate window to change or query a variable's value. To view the content of a variable, enter a ? character followed by the name of the variable and then press Enter. You can change a variable's value by entering VariableName = NewValue in the Immediate window and pressing Enter.

If the Immediate window is not visible (typically at the bottom of the VBA Editor screen), press Ctrl+G or choose Immediate Window from the View menu to make the window visible.

To remove a breakpoint, put the cursor on the line of code and press F9. You can clear all breakpoints by choosing Clear All Breakpoints from the Debug menu or pressing Ctrl+Shift+F9. VBA also provides the Stop command. This simply stops code execution on that line of code and enters break mode.

Once you are finished debugging the code, be sure to go back and clear all breakpoints (choose Clear All Breakpoints from the Debug menu or press Ctrl+Shift+F9) and be sure to remove or comment out all Stop statements.

When you are paused at a breakpoint or in step-by-step mode, you can change the next line to be executed, either before the current line to re-run a section of code, or after the line to skip statements. Right-click the line where you want execution to resume and right-click and choose Set Next Statement or choose Set Next Statement from the Run menu. Execution will resume at the selected line of code.

The Debug Command

VBA provides a Debug object with two properties, Print and Assert that you can use display a variable's value and to control the program flow. Debug.Print will write what follows it to the Immediate window. Code execution is not interrupted. After displaying the text in the Immediate window, code execution continues to run. You can mix literal text with variable names in the Debug.Print statement. For example,

Debug.Print "The value of variable X is: " & X

You can display several variables at once in the Immediate window by separating them with commas. For example,

Debug.Print X, Y, Z

The Debug.Assert command is a conditional breakpoint that will cause execution to pause on the Debug statement if the expression that following the Assert statement is False. For example,

Debug.Assert Var >= 0

This will pause on the Debug.Assert statement if Var >= 0 is False; that is, it will pause if Var is negative. It may seem backwards that execution is paused when the condition is False rather than True, but the Assert method was adopted from the C language, and its usage remained the same as in C.

Be sure to remove or comment out the Debug.Print and Debug.Assert statements when you are finished debugging. You generally don't want these statements to be operative during normal usage of your application.

The Locals Window

The Locals windows allows you to view the value of all the variables in a procedure when you are stepping through the procedure. To display the Locals window, choose Locals Window from the View menu. Using the Locals window is easier to display variable values than examining the value from the Immediate window. For simple variable types (e.g., Long and String variables), the value is displayed on one line. For complex types or objects (e.g., a Range variable), its properties are displayed in a collapsible tree-like structure.

The Watch Window

The Watch window displays all the Watches in effect. You can display the Watch window by choosing Watch Window from the View menu. A Watch is an instruction to VBA to pause code when an expression is True or when the variable being watched changes value. To create a Watch on a variable, open the Watch window and right-click in the Watch window and choose Add Watch... from the popup menu or choose Add Watch... from the Debug windows. In the Add Watch dialog, enter in the Expression text box a variable name whose value you want to watch. Then choose Break When Value Changes. When you run the code, execution will pause at the line after the line that modifies the variable's value. When code pauses, the value of the variable will have already been updated.

To remove a Watch, right-click it in the Watch window and choose Delete Watch from the popup menu. To modify a Watch, right-click it in the Watch window and choose Edit Watch from the popup menu.

The Call Stack

The Call Stack is a data structure maintained by VBA that tracks which procedure called another procedure. For example, if procedure AAA calls BBB which calls CCC, the Call Stack window will display the list of procedures starting with the most recent procedure and below that, the chain of procedures that were executed to get to the current position. You can view the Call Stack by choosing Call Stack from the View menu. This is useful to track the flow of execution that ended up in the current location. Unfortunately, there is no programmatic way to get information from the call stack.

_{Source: Chip Pearson at Pearson Software Consulting: http://www.cpearson.com/Excel/DebuggingVBA.aspx}