Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

The Watch Window

The Watch window is another amazingly multifunctional debugging tool provided by the VBE. Inexplicably, there is no direct keyboard shortcut available to display the Watch window. You can, however, use the standard Windows menu hotkey sequence Alt+V followed by an h character.

The Watch window is typically used to display the value of variables or expressions that you have specified while you are stepping through your code in break mode. But the Watch window contains two features that make it invaluable as a run-time debugging tool as well: Break When Value Is True and Break When Value Changes. Both of these features are discussed at length later in this section. Unlike the Immediate window, the Watch window does not operate at design time.

Setting a Basic Watch

The most fundamental feature of the Watch window is its capability to show you the value of a variable or expression that you specify in real time while you are stepping through your code. Adding a watch is very easy, but inexplicably, it is another fundamentally important operation involving the Watch window that has no keyboard shortcut. Therefore, the easiest way to add a watch is to highlight the variable or expression that you want to watch, right-click the highlighted area and choose Add Watch from the shortcut menu. The process of adding a watch is shown in Figure 16-10, Figure 16-11 and Figure 16-12.

Figure 16-10. Specifying the Watch Expression

[View full size image]

Figure 16-11. Configuring the Watch Expression

Figure 16-12. The Completed Watch Expression

[View full size image]

The watch expression added is shown in Figure 16-12. This sequence assumes that you simply accepted all the default values in the Add Watch dialog shown in Figure 16-11. We discuss why and when you might want to change these defaults later in this section.

As you can see, the Watch window displays the expression being watched, the value of the expression, the data type of the expression and the code module and procedure name within which the watch was defined. You can add as many simultaneous watches as you like. Each watch will be shown on a separate line in the Watch window.

Using a Basic Watch

As you step through your code in break mode, the Watch window will continually update the values of all of the watches you've added. This is the primary purpose of the Watch window. There are typically a large number of things going on in your code and the Watch window provides you with a method to monitor exactly what's happening to all of the critical variables and expressions in the code you're debugging.

The Watch window enables you to modify the value of any variable or expression that is an lvalue. An lvalue is just a fancy computer science term that says the variable or expression can appear on the left side of an assignment. In the following line of code, for example, the expression Sheet1.Range("A1").Value is an lvalue because it is valid for this expression to appear on the left side of the assignment operation, which in this case assigns it the value of 25:

Sheet1.Range("A1").Value = 25

By contrast, the expression ThisWorkbook.Worksheets.Count is not an lvalue because you cannot simply assign it a new value. It can only be changed by physically adding or removing worksheets in the workbook.

To modify the value of an lvalue expression in the Watch window, just click the Watch window Value column in the row containing the value you want to modify. The Value column entry will turn into an editable field and you can type in a new value. The new value must be a data type that is valid for the expression you are altering. Figure 16-13 shows us changing the value of the expression Sheet1.Range("A1").Value to 99.

Figure 16-13. Modifying the Value of a Watch Expression

[View full size image]

You can also edit the Expression column of the watch. In the case of Figure 16-13, for example, you could change the watch to point at Range("B1") instead of Range("A1"). Don't be too concerned about trying to determine what you're allowed to change. If you make a mistake, the VBE will display an error message and the Watch window will revert to its previous state. No harm done.

Watch Types

When you create a watch expression, you don't have to accept the default values of all the options in the Watch window. By modifying these defaults you can create watches that are much more powerful than those that simply use the default values.

There are two option categories that you can modify when you add a watch: Watch Context and Watch Type. We cover them both in following sections. Don't worry if you don't get the values of these options correct when you first add the watch. You can edit any existing watch, which allows you to modify these options. The steps required to edit a watch are shown in Figure 16-14 and Figure 16-15.

Figure 16-14. Right-Click over a Watch Expression to Edit It

[View full size image]

Figure 16-15. Change the Watch Type to Break When Value Changes

Watch Context

The Watch Context options control the scope of the watch expression. Watches can be confined to code executing within a single procedure in a single module (the typical default) or they can apply to code executing anywhere within the project.

Suppose, for example, you want to place a watch on a global variable or expression. If you simply add a watch on that variable or expression from within the first procedure where you come across it, the watch will only be valid when code is executing within the procedure where the watch was added. To change this, you change the selections in the two Watch Context drop-downs.

Module

We are going to discuss the Watch Context options in reverse order of their appearance on the Watch dialog because the Module setting drives the Procedure setting. You have two options when selecting a Module setting:

Procedure

The Procedure setting determines what procedure the watch expression will be valid for within the module specified by the Module setting. As described above, if the Module setting value is (All Modules), then you have no choice over the Procedure setting. In this case, its only possible value will be (All Procedures). If a specific code module has been selected in the Module setting, there are two options for the Procedure setting:

Watch Type

The Watch Type setting will determine how the Watch window handles the watch. The first option is passive, used only while stepping through code in break mode. The second two options are active and are used to initiate break mode.

Watch Expression

This is the default value for the Watch Type setting. It just adds the specified variable or expression as a watch and displays its value while you are stepping through your code in break mode.

Break When Value Is True

This Watch Type setting has much in common with the Excel conditional formatting expressions we discussed in Chapter 3 Excel and VBA Development Best Practices. When you specify this watch type, your watch is treated as a Boolean expression and code execution will stop and enter break mode whenever the value of the expression changes from False to True or <out of context> to True.

This type of watch can be constructed as either a Boolean expression or a simple watch expression whose only possible values are True or False. Regardless of the way in which you construct the watch, code execution will stop and enter break mode whenever the value of the watch evaluates to True.

One very common use for this Watch Type setting is somewhat counterintuitive but very valuable in practice. The Application. EnableEvents property is persistent from the time it is set to False until it is explicitly set to True or Excel is closed, which ever comes first. While this property is False, all Excel events are disabled. One of the most frequent Excel programming bugs is to set Application.EnableEvents to False and then forget to set it back to True when you no longer need to disable events. This will obviously cause havoc in any application that depends on trapping Excel events for its operation.

We can very easily debug this problem by telling the Watch window to break code execution whenever the Application.EnableEvents property does not equal True. If the Application.EnableEvents property does not equal True, by definition it has been set to False. After you have set this watch, each time code execution breaks you know you have turned off Excel events. You can then examine the code that follows and ensure that Application.EnableEvents has been properly reset. We demonstrate setting this watch expression in Figure 16-16.

Figure 16-16. Setting a Break When Value Is True Watch

Note that the watch expression we have defined is Application. EnableEvents <> True. When this expression evaluates to True, it means that Application.EnableEvents has been set to False. (If Application.EnableEvents does not equal True, it must be False.) Also note that we have set the Context of this watch to (All Procedures) and (All Modules). This is because the Application.EnableEvents property is global to the current instance of Excel, regardless of where it has been set.

Break When Value Changes

Another common situation you want to watch for is when the value of an expression or variable in your code changes. In this case you are typically not concerned about the specific value to which your variable or expression changed, rather you want code execution to break whenever that value changes to anything other than its current value.

This type of watch can be constructed as either a Boolean expression or a simple watch expression. Regardless of how you construct the watch, code execution will stop and enter break mode whenever the value of the expression changes. In Figure 16-17 we set a watch that will cause code execution to stop and enter break mode whenever the value of the expression Sheet1.Range("A1").Value changes.

Figure 16-17. Setting a Break When Value Changes Expression

Note that we have set the Context settings for this watch to a specific module and procedure. This means code execution will only break when the value of the watch expression is changed by the specified procedure or one of its subprocedures.

Arrays, UDTs and Classes in the Watch Window

Simple variables and expressions added to the Watch window are easy to understand on sight. But the Watch window is much more powerful than this. It can easily handle complex data types such as arrays, UDTs and classes. Watches for these data types are added in exactly the same way that watches for simple variables are added, but the results are quite different.

Recall that in Chapter 13 Programming with Databases we created a BILLABLE_HOURS UDT to hold information about a billable hour entry from our timesheet application. Listing 16-5 shows a section of code from the PostTimeEntriesToDatabase procedure that uses this UDT.

Listing 16-5. Code That Uses the BILLABLE_HOURS UDT

Dim uData As BILLABLE_HOUR
For Each rngCell In rngTable
uData.lConsultantID = rngCell.Value
uData.dteDateWorked = rngCell.Offset(0, 1).Value
uData.lProjectID = rngCell.Offset(0, 2).Value
uData.lActivityID = rngCell.Offset(0, 3).Value
uData.dHours = rngCell.Offset(0, 4).Value
If Not bInsertTimeEntry(uData) Then
Err.Raise glHANDLED_ERROR
End If
Next rngCell

Let's assume that we are debugging this code and we want to watch the contents of the BILLABLE_HOURS UDT. There's no need to add each individual element of the UDT to the Watch window. Just add a watch on the uData UDT variable as shown in Figure 16-18.

Figure 16-18. Adding a Watch on a UDT Variable

Figure 16-19 shows how the Watch window displays a UDT watch expression when you are stepping through code.

Figure 16-19. Using a Watch on a UDT Variable

[View full size image]

Even though we only added a watch to the uData variable, the Watch window understands this is a UDT and it displays the member variables of the UDT in a hierarchical list below the variable the watch was defined on.

Array variables and objects variables are treated the same way. The Watch window recognizes their data types and displays the current values of all their members in a hierarchical list similar to that shown in Figure 16-19. This is a great way to learn the object model of the application you're working with. Set a watch on an object variable, trigger break mode once that variable has been set and then use the Watch window to drill down through that object's properties and child collections.

Quick Watch (Shift+F9)

The Quick Watch window is the little brother of the Watch window. By highlighting a variable or expression while in break mode and pressing Shift+F9, the Quick Watch window allows you to examine all the same details that would be displayed by the Watch window except for the data type. The Quick Watch window also allows you to quickly add the selected variable or expression to the Watch window by invoking the Add button (Alt+A). Figure 16-20 shows an example of the Quick Watch window being used to display the contents of a string variable.

Figure 16-20. The Quick Watch Window

The Quick Watch window is designed for hands-on-the-keyboard debugging. If you're using the mouse, in most cases the Quick Watch window will be unnecessary. This is because the VBE will dynamically display the value of most expressions in a tooltip when you hover your mouse cursor over them. This behavior is shown in Figure 16-21.

Figure 16-21. Tooltip Expression Evaluation