VBA Beginner's Corner - Someone to Watch over you.
The Visual Basic for Applications (VBA) integrated development environment (IDE) seen in the Microsoft Office applications contains lots of tools to help you diagnose and fix errors in your code. However, many of us don't take advantage of the tools that are there. Personally, the technique I relied on most heavily when I was first learning VBA was the message box. If I wanted to see the value of a variable during execution of my code, I used the Msgbox function to display the value of an object, expression, or variable. It was simple and quick. However, this method has some serious disadvantages. It was static, meaning that it only gave me the value of the variable during the first few iterations of my code. The only way I could see the changes to a variable was to dismiss the message box manually during each iteration of my code. This is very tedious if, for example, you want to see the value of an expression on the 25th time through my code. It was also easy to forget about a message box I had added earlier and be interrupted when it popped up. I'd have to stop the code and then delete the msgbox box. It was also very limited in showing me the interaction between different parts of my code.
Another method I use is to halt execute and then step through it one statement at a time. This is more effective than the message box but was still somewhat tedious. Especially if you have to step through dozens of statements one step at a time.
Fortunately, the IDE has many tools that are much more dynamic than the message box. One of the most powerful are Watch expressions. A Watch expression is a VBA (or Visual Basic for that matter) variable, function value, property value, or other valid expression that shows you its value when the code pauses, for example, at a break point or when the value of another variable hits a certain value. Watch expressions help you track the values of items as your code executes. (A break point temporarily pauses your code so you can debug it.) Not only are Watch expressions great tools to use to inspect your objects and collections at runtime, but they are also a good teaching tool to help you when trying to understand objects and how they are constructed. What’s most powerful about adding watches is that in addition to variables, you can also add expressions, and options to break when the value changes. The latter is particularly powerful when you are having trouble determining why a particular situation arises in your application. Maybe a variable is set in multiple places and you can’t tell which instance is causing the value to change. By setting the Watch Type option, you can quickly stop when this occurs.
Watch expressions are displayed in the Watch window. To see an expression in the Watch window, just highlight the expression in the code window and drag it to the Watch window. You can display the Watch window by clicking the View menu and then clicking Watch Window.
There are three types of Watch expressions in VBA: the Watch expression, Break When Expression is True, and Break When Expression Has Changed. The Break When Expression is True and Break When Expression Has Changed options allow you to specify a logical condition or expression. When that condition is met or the expression hits a certain value, VBA halts execution and places you in Break mode.
You can add, edit, or delete a Watch expression at design time or during a break in the code. In the VBA IDE, to add Watch expressions, just click the Debug menu and then click Add Watch. This displays the Add Watch dialog box. Once you've defined an expression and it appears in the Watch window, you can edit it by clicking the Debug menu and then clicking Edit Watch. The Add Watch and Edit Watch windows have many of the same components. The main difference is that the Edit Watch dialog box has a Delete button. Looking at the parts of the Watch window: the Expression box contains the expression to be evaluated; the Value box is self explanatory; the Type box sets how VBA responds to the watch expression such as when you enter break mode or when the expression hits a true (non-zero) value or when the value changes the Context option sets the scope of the variables being evaluated, for example, if you are watching variables with the same name or you want to restrict scope to a particular subroutine.
To try an example in Office, do the following:
1. Start your favorite Office application such as Access, Excel, or Word and open new database, workbook, document, as appropriate.
2. On the Tools menu, click Macro, and then click Visual Basic Editor.
3. In the VBE, click Insert, and then Module. Double-click Module 1 in the Project pane to open the Code window.
4. Add the following code:
Public Function TestWatch()
Dim x, y, z As Integer
For x = 1 To 10
For y = 1 To 20
z = x + y
Next y
Next x
End Function
5. Highlight just the x + y portion of the expression and on the Debug menu, click Add Watch.
6. The Add Watch dialog box is displayed with the expression in the Expression box.
7. Under Context, select the context in which the expression will be evaluated. In this case, leave the default which is the TestWatch function.
8. Click OK. The Watch window is displayed with the expression. Notice the glasses icon next to the expression.
9. Set a breakpoint in your code by highlighting the second Next statement in your code and then clicking Toggle Breakpoint on the Debug menu. You can also click the gray margin to the left of the statement to create a breakpoint.
9. Open the Immediate window by clicking the View menu, and then clicking Immediate Window.
10. In the Immediate window, type TestWatch and press Enter. Notice that the Value box beside the expression now equals 22. However, also notice that z (the result of the addition) equals 21. This difference results because by putting the break statement on the second Next statement, x has already incremented but z won't change until the next iteration through the loop. (Thanks to Tim Rohr for pointing out the error in my original post)
11. To get out of break mode, on the Run menu, click Reset. Click the Debug menu and then click Clear All Breakpoints.
12. Now, highlight the expression in the Watch window, right-click, and then click Edit Watch.
13. In the Edit Watch dialog box, change the expression to y = 9, select the Break When Value Is True option, and then click OK. Now the icon beside the expression has changed.
14. Click in the Immediate window and press Enter.
15. Code execution stops when the value of y reaches 9. Now hover the mouse over the z in the expression in the subroutine and notice that it is equal to 9. Then do the same for x and y. Notice they are equal to 1 and 9, respectively. The reason that z is equal to 9 is that although the code stopped when y equaled 9, the code hadn't executed the z = x + y expression yet.
16. To execute the expression, on the Debug menu, click Step Into. Notice that the yellow highlight moved one statement down to the Next statement.
17. Now hover the mouse over the z variable. The value is now 10.
18. Again, click Reset on the Run menu, and then highlight the expression in the Watch window, right-click, and then click Edit Watch.
19. In the Edit dialog box, change the expression to z, and then click Break When Value Changes. The icon changes again.
20. Run the code. Notice that in the Watch window, the Value box now equals 2 and the code is highlighted at the first Next statement. As we specified, the code changed when the value of z changed.
21. Now reset the code and try example expressions of your own.
As a note, if you don't need to continually monitor a value, you can use the Quick Watch dialog box, available from the Debug menu, to quickly check the value of a variable or expression. Also rather than examining variable values individually, you can see all the local variables by selecting Locals Window from the Views menu. This displays the entire list of local variables and their current values. Local variables are variables defined in the current procedure and module declaration section.
For you more advanced VBA'ers, if you have some tips and tricks when using the Watch window, send them to me and I'll include them in this post.