Debugging VBA Code
This page describes methods for debugging your VBA code.
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.
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.
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.
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 interupted. 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 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 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
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.
||This page last updated: 5-Jan-2013.