Microsoft Excel Power

Lesson 2: The Basics of VBA

Testing and Debugging code

When you run your macro you are in effect testing it, and hopefully, it will do what you require of it. Generally, the resulting spreadsheet will indicate the success of the macro, but sometimes it can go terribly wrong! So, what do we do in these circumstances? Well, VBA has an outstanding code development environment and one of its best features is the debugging tools.

As before, when I prepared the spreadsheet view to record and edit macros, I prepare the edit environment to handle debugging. Go to the Code Edit screen and select View->Toolbars and check the debug toolbar. The debug bar will popup, then just place it on the menu.

Fig 2.04 - Selecting the Debug toolbar.

Fig 2.05 - The Debug Toolbar.

All of these options are explained in the texts, but the more important ones are as follows:

Fig 2.06 - The Step Over option.

Fig 2.07 - Step to Cursor option.

Fig 2.08 - The Quick Watch option.

The debugger gives us the option to trace the path of execution as it progresses through the code. We can display variables to determine their values, and by stepping through the lines of code one at a time, we can quickly find the problem. Once found, we can correct the code and start the test again.

To invoke the debugger we first set a breakpoint. A breakpoint is just a place where the code will stop and wait for us to control it from there. To set a break point, you choose the appropriate point in the code - generally a point near to where it seems to go wrong, and then press the "F9" key. You will then see a red dot at the left most point of the line of code indicating that the breakpoint has been set (Fig 2.09).

Fig 2.09 - A breakpoint has been set.

Notice also that the statement at the breakpoint is highlighted in red. When we run the macro it will automatically stop at this point. I can then step through the lines of code by selecting the Step Over button (Fig 2.06). However, before I set the macro running, I'm going to set some Quick Watches. These are displays of important variables. In this code example, the loop, the variables that I want to see would be the value of i and the expression Cells(i, "C").Value.

These two values will tell me most of what is going on with my code. To set a Quick Watch, highlight the variable or expression you are interested in and select the Quick Watch button (Fig 2.08). When you do this, the following dialog will popup (Fig 2.10). Select the Add button and the expression will be added to the watch list - if this is the first one to be added, the watch window will open up as shown in Fig 2.11.

Fig 2.10 - The Quick Watch confirmation dialog.

Fig 2.11 - The watches.

As you step through the code, these variables and expressions will reflect their values to help determine and resolve problems with the code.

Fig 2.12 - The watches as we step through the code.

Here we see the current line of code being executed - highlighted in Yellow, and the values of Cells(i, "C").Value and i at this point. From this information we can usually determine the problem.

  1  2  3  4  5