BaldyWeb

Debugging VBA Code

Setting a breakpoint

Often, when debugging an application, you will want to stop code execution at a certain point. One way to do this is to set a breakpoint.

Setting a breakpoint is very easy, just click on the thin left grey margin of the module code window. A red dot will appear in the margin, and the corresponding line of code will be highlighted in red. The line of code must be executable, that is you can't set a breakpoint on a line that is blank, only a comment, a Dim statement, etc.

Now, when the breakpoint containing procedure is executed, the program will stop immediately before that line of code executes. At this point, you can view variables and values in the Locals window, test expressions in the Immediate window, and do whatever else you need to do for debugging. The VBE window should show "[break]" in the title bar indicating you are in break mode; you may also hear this referred to as debug mode.

After you are done with your immediate debugging needs, you can continue code execution (one way is to press {F5}). You can make changes that you will not be able to continue from; in these cases you should see a warning box before the change is made and (if the change is accepted), you will have to restart code execution.

Setting a breakpoint and examining code in debug mode will probably be a common event for most developers, so it will serve you well to become familiar with this process.

• Step Into - {F8} - Moves to the next line of code, if that line calls a routine, it will step into that routine.
• Step Over - {Shift + F8} - Moves to the next line of code, if that line calls a routine, it will execute, but not step into the routine.
• Step Out - {Ctrl + Shift + F8} - If you are in a routine, this will complete execution of the routine, and step back to the calling routine. If you are in the top-most routine, it's the same as Continue
• Run to Cursor - {Ctrl + F8} - If you place the cursor on an executable line, the code will execute until that line is reached (if possible).
• Continue - {F5} - Continue code execution as normal. You can set another breakpoint before continuing if you like.
• Set Next Statement - {Ctrl + F9} - Set the line of code that you want to be executed next. You can use this to skip forward or backward in your code.

Step Into and Continue are the most frequently used, but you can save a lot of time using Step Over to avoid stepping though called routines that you know are executing properly.

Just 2 general notes you may find helpful when debugging and stepping through code:

Remember that the debugger stops on a line before the line is executed. If the situation is such that it is difficult to determine which line is causing the error, I like to set a breakpoint at the first possible line, and as I step through the code, move the breakpoint forward. This saves time if I need to let the procedure run several times, by continuously moving the breakpoint closer to the problem line - that way I both eliminate the need to step through the same lines of code again, but I can easily see the line causing the problem.

For the same reason, keep in mind if you are doing variable assignment on the current line, the line has not executed yet, so you are looking at the old (and possibly uninitialized value) of the left-hand-side (LHS) variable. Don't be confused by this fact. If the RHS is a function returning the value, now might be a good time to call it from the immediate window to check the return value before stepping through called function.

Debugging requires its own mindset and techniques, and knowing the tools available to you will help you develop good debugging skills. Understand the various ways to step through your code so you can efficiently debug your application.

The Immediate Window

While you are in debug mode, you can examine the values from variables, form references and such in two ways.  You can hover the mouse over the variable in code, and the value should be displayed by a "tool tip" type popup.  You can also use the Immediate window.  Typing

?VariableName

into the Immediate window and hitting enter will result in the variable's value to be printed out to the Immediate window.  This is particularly useful when building SQL in code, since you can copy/paste the SQL into a blank query for testing purposes.  More info on that here:

Using the Immediate Window

JasonM

Home