14. Debugging

14.1 Testing a Macro

All macros need to be tested to make sure they operate correctly, and as expected. This is true even of macros where all commands were recorded, but is particularly important where you have created macro code in the VBA Editor. Mistakes are made!

If you save your macros in the Personal Macro Workbook, that workbook serves as the container workbook, and when the macros are run they affect the active workbook. As you test the macro, you will be changing both these workbooks. The Personal Macro Workbook is changed when you make changes to a macro. The active workbook is changed as you run the macro. This means that during the testing process you will want to save the Personal Macro Workbook. The active workbook, however, should not be saved during the testing process. You will be testing repetitively, and you will always want to start each round of testing with the active workbook in its original state.

Unfortunately, it is all too easy to inadvertently save the active workbook during testing. As a precaution, I strongly suggest that before starting to test, you make a backup copy of the active workbook, and always keep that backup in its original state. That way you can recover if you accidentally save the active workbook in some "interim" state while testing the macro. As stated earlier, mistakes are made.

14.2 Syntax Errors vs. Logic Errors

There are two kinds of errors that can occur when programming macros: syntax errors and logic errors.

Syntax errors are errors in VBA statements, such as a misspelled function name or For without a corresponding Next. Those types of errors will generate an error message when a macro is compiled, as described earlier in this tutorial. Syntax errors also include errors in dot notation, such as calling a method on an object which does not have that method. Those errors will generate a VBA error message when a macro is run. Because syntax errors generate error messages they are relatively easy to identify and fix, although some error messages may not identify the problem as clearly as we might wish.

Logic errors are more subtle. There is no problem with the syntax of the macro and it will compile and run without any obvious problem, but we discover that the macro does not do what we wanted and expected it to do. Programming errors that cause such unexpected or undesirable results are called logic errors, popularly known as "bugs." The process of identifying and eliminating logic errors is called "debugging."

14.3 Stepping through a Macro

The primary debugging operation is to execute the macro statements one at a time in the VBA Editor; this is known as "stepping through" the code. While you are doing that, you can watch what happens on the worksheet where the macro is operating, step by step.

First, remember that the macro will run on the currently active worksheet, so make sure the correct one is active. If the macro is designed to be run when the cursor is at a certain point, you need to first position the cursor at that location before debugging the macro (if the macro does not do that as its first step, which it should).

To step through a macro, open the macro in the VBA editor. Position the cursor in the macro code, then click on Debug/Step Into (or press F8). The next statement to be executed is highlighted in yellow, and indicated by an arrow in the margin. Each time you click that menu item (or press F8), the highlighted statement is executed, and the next statement to be executed is highlighted. By this process you can watch the effect of each statement on the worksheet.

Once you have identified a problem, you can stop the macro by clicking Run/Reset. You can then correct the macro code, and save the changes you have made to the macro by saving the Personal Macro Workbook. Since the macro has partially run the active workbook has been changed, so you should close it without saving, and reopen it for the next round of debugging. If you save it accidentally, you can use your backup copy to recreate it in the state it was before the macro is run. To do so, make a copy of the backup, and rename it (keeping the backup copy unchanged).

14.4 Setting Breakpoints

Often the problem does not lie at the beginning of the macro, and you would like to have the macro run normally up to the point where the problem is. You can do that by setting a breakpoint. When you run the macro by clicking on Run/Run Sub/Userform in the VBA editor, the macro will run uninterrupted up to the breakpoint; when it gets to the breakpoint it will highlight the statement and wait for you to begin stepping through the rest of the macro as above.

To set a breakpoint, navigate to the statement where you want the breakpoint and click in the margin on that line (or position the cursor on that line and click on Debug/Toggle Breakpoint, or press F9). A dot will appear in the margin, indicating the breakpoint. Doing that again toggles the breakpoint on or off.

You can have multiple breakpoints in the macro; to run the macro up to the next breakpoint click on Run/Continue. To remove all breakpoints, click on Debug/Clear All Breakpoints.

14.5 Checking the Value of a Variable

While you are debugging it is often necessary to determine the current value in a variable. You can easily do that while you are stepping through the macro by hovering the cursor over the variable in the code. A small bubble will appear showing the value in the variable at that point. Remember that when stepping through, the highlighted statement has not yet been executed. That means the value of any variable you check is the value before that statement is executed. If the highlighted statement is a variable assignment, the value of that variable is the value before that assignment is executed.

14.6 Other Debugging Tools

The VBA Editor has additional debugging tools, such as other commands on the debug menu, the watch window, and the immediate window. As you become more experienced and your macros become more complex you will probably want to become familiar with the other debugging tools that are available.