Five tips for debugging a routine in the Visual Basic Editor

The VBE provides tools to help you debug your code, but you need to know a few tricks to use them effectively. Susan Harkins suggests a few ways to streamline your debugging tasks.

Debugging your code lets you find and resolve errors before your users run into them. Fortunately, the Visual Basic Editor provides a number of helpful tools for debugging your VBA code. The following tips for debugging go a step beyond using the tools and offer recommendations for making the process smoother and more efficient.

1: View evaluated strings in the Immediate window

Few debugging chores are more frustrating than debugging an evaluated string. That's because the hoops you jump through to concatenate the pieces aren't intuitive — you can't just look at the statement and see that you're missing a space, a quotation mark, and so on. (Well... most of us can't.) When you need to concatenate strings to create a SQL statement, connection string, and so on, assign the evaluated string to a variable and then insert a Debug.Print statement immediately following the definition statement. That way, you can see the evaluated line in the Immediate window, as shown in Figure A, where mistakes are much easier to spot.

Figure A

Display evaluated results in the Immediate window for quick debugging.

2: Use Debug.Print liberally

The Debug.Print statement (tip 1) is helpful during all stages of debugging. And once you're done, you don't need to remove them from the code, as they're harmless. Here are a few other good spots for Debug.Print statements:

  • Verify passed arguments by using Debug.Print at the beginning of a function.
  • Verify conditional values in decision-making statements such as If...Then and Select Case.
  • Track loop values and verify that the loop executes the expected number of times.
  • Print a function's value after processing to check its results.

Keep in mind that the Debug.Print only prints values to the Immediate window so that you can easily view them. It's a good way to discern that a value isn't what you expected so you can take corrective action.

3: View variables using data tips

Pressing [F8] lets you step through the current procedure line by line so you can watch the code execute. While doing so, you have an opportunity to discern the value of variables and properties. Of course, there's more than one way to accomplish this goal, but keeping up with your variables as you step through the code can be invaluable.

To enact step mode, press [F8] or choose Step Mode from the Debug menu. Then, press [F8] to step through each line of code. Once a line of code defines a variable, you can hover the mouse over the variable to view its value, as shown in Figure B. Data tips also work in Break Mode.

Figure B

Use the mouse to view a variable's value.

If this feature isn't working, enable it as follows:

  1. Choose Options from the Tools menu.
  2. Cick the Editor tab.
  3. Select the Auto Data Tips option in the Code Setting section.
  4. Click OK.

4: Use Option Explicit

This tip improves efficiency by eliminating errors before they work their way into your code. When enabled, this option forces you to explicitly declare a variable's data type. An undeclared variable generates a runtime error. There's a side benefit that isn't immediately obvious, though. If you misspell a variable, the debugger will complain when it happens — not later when you execute the code and it fails to work correctly.

You can manually enter an Option Explicit statement at the top of a module. Or enable it as follows, and the VBE will include the statement in every new module:

  1. Choose Options from the Tools menu.
  2. Click the Editor tab.
  3. Select the Require Variable Declaration option in the Code Settings section.
  4. Click OK.

5: Display the Debug toolbar

During the debugging process, be sure to display the Debug toolbar. There are several tools you'll want quick access to: Step Into, Step Over, Stop Out, Watch Window, and Locals Window, just to name a few. I also recommend that you add the Compile Project button to the Debug toolbar as follows:

  1. Right-click any menu or toolbar and choose Customize.
  2. Click the Commands tab.
  3. Select Debug in the Categories list.
  4. Drag Compile Project from the Commands list to the Debug toolbar.
  5. Click Close.

You can choose to compile from the Debug menu, but having the tool readily available on the toolbar is a good reminder to compile frequently.


Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

Editor's Picks