---

Watch Expressions

Another extremely useful debugging feature is the 'Locals Window', which can be activated from the View menu. This window displays all the locally declared variables in a macro, listing not only their types (string, integer etc.), but also their current values. As you single step through your macro you can watch as the variables are assigned different values and thus find out if this is the cause of a macro error.

As an example let's turn go back to our sample macro to look at how the variable 'number' changes as the code executes.

Making sure that we are in the module containing the macro code, we can press F8 or click on the Single Step button on the toolbar to open the debug window. Now open the 'Locals Window' to see the listing of the variables. As you single step through the code the value of 'number' will change, and, finally, the string variable 'Finished' will also be assigned a value before it is used. Note that moving the cursor over one of the variables in the code while in Break mode will display a tool tip containing the current value as well.

The Locals Window provides useful information, but it cannot be used actively to control program execution in any way. What if you want to monitor a variable and stop execution when it reaches a given value? Rather than try and single step until you reach that point you can actually configure a 'Watch' expression, which will be monitored until it is true and then the macro will halt and leave you in Break mode.

To create a Watch expression use the View menu, click on the appropriate icon on the toolbar. Alternatively place the cursor in the code window and then click on the right-hand mouse button. From the menu that appears select 'Add Watch', and this will open the 'Add Watch' dialog. The 'Expression' text box allows us to enter an expression we wish to track, which may be the name of a variable, a logical expression, an object's property and so on. In this example let's limit ourselves to watching the value of the 'number' variable - which we can just enter into the text box. It is also possible to set up an instant watch by high-lighting a variable or line of code, right-clicking the mouse button and selecting 'Add Watch' from the context sensitive menu.

The 'Context' section of the 'Add Watch' dialog allows us to select which modules and procedures we are interested in. Remember that a complex Excel application may consist of many module sheets and possibly hundreds of functions and procedures.

Finally, the 'Watch Type' option group allows us to fine tune the action of our watch expression. The first option simply allows us to watch the value of the expression as the macro is single stepped. If we select this option (which is the default, by the way), we can now begin to single step through the code. Notice that before we've even started to single step that the variable has taken on the value of '0'. As we step through the code the value changes to '1' as soon as we execute the first line of the For…Next loop. From then on the value of the variable changes each time we execute the 'Next' statement. The value of the variable eventually clocks up to '8', at which point it is greater than the maximum we set for the loop and it exits from the loop. At this point the variable does not re-set itself but it retains the final value of 8. Knowing this of course means that we could, conceivably, use the value of the variable elsewhere in our code, should we want to.

The other 'Watch Type' options are also worth exploring. The 'Break When Value Is True' option means that you can run the macro and it will execute continuously until the given watch expression evaluates to a logical True. For example instead of merely entering 'number' as our watch expression, we could have entered a logical test, such as 'number = 3', which means that we could have let the macro run until the value of the variable 'number' is equal to 3, at which point execution would stop and we'd be in debug mode. Finally, the last option is 'Break When Value Changes', which means that the macro can execute until the value of the given expression is changed.

All three of these options provide useful debugging functionality, and which one to use will depend largely on the nature of the problem you are tracking down. You may add and delete 'Watch' expressions interactively whilst debugging your code, there is no need to set them up at the beginning of a session. Moreover, once you have set up a watch expression it remains in force until you either delete it or close down that Excel session. In practice this means you can set up an expression, use it, go back to the code, re-run it and still have access to the same 'watches'.

Breakpoints

A more traditional way of debugging software is to insert break-points in the code, which means that a program can execute normally until it hits a designated line of code at which point it will stop and you can go into debug mode. If you already know roughly where a macro is going wrong, then it is more straightforward to use a breakpoint than it is to use a 'Watch' expression.

Setting a break-point is as simple as placing the cursor on the line of code you want to stop at, then pressing F9 or clicking on the 'Toggle Breakpoint' button on the toolbar. The entire line of code will then become high-lighted to signal that it is a break-point. If you then start the macro it will execute until it hits the break-point, at which point execution will halt and you will be in Break mode. Note that the break-point line itself is not executed, execution is pending. At such a point you may chose to single step through the next block of code, or else set a watch statement or use the immediate window to examine the values of the macro variables.

Break-points can be toggled on or off dynamically, so that you may add a break-point, halt execution and then depending on what you find you may want to continue execution where it had stopped, with perhaps a new break-point further on in the code.

As with 'Watch' expressions, break-points are 'remembered' so that they can be used again if you decide to completely halt execution of a macro. An alternative to setting a breakpoint is to place the cursor on the line of code at which you want to halt execution and then select 'Run To Cursor' from the Debug menu (or press Ctrl+F8).

Armed with break-points, 'watch' expressions and the ability to single step through code, it is possible to code and debug quite complex macros to create complete Excel applications. However, a set of macros need not be that complex before you find that you need to explore the properties and functions associated with Excel's different objects: workbooks, worksheets, ranges etc. That's where the Object Browser comes in handy.

Object Browser

To access the Object Browser click the Object Browser button, hit F2 or select from the View menu within the VB environment. The combo box at the top left of the dialog can be used to select which objects you want to browse, the default being for the current workbook. However selecting Excel gives a complete list of all the objects supported by Excel. Selecting an object from the list throws up a corresponding list of methods and properties. Where arguments exist for a method, such as with the Cells method which takes a row and a column index as an argument, the list of arguments is shown below the Objects/Modules list. The other combo box allows search strings to be entered, which makes finding a particular object, property or method even easier.

Clicking on the question mark button will open the help file for that method or property. You can also paste the currently selected method or property into your current macro by clicking on the Paste button.

Conclusion

As should be clear from this article, Excel continues to provide a very functional debug environment for VBA code - be it recorded macros or functions and applications you build yourself. For any Excel super-user learning to use VBA and the Visual Basic environment is an essential step towards getting the most functionality from the most popular spreadsheet application in the world.