The Watch Window can be a powerful tool in your VBA bag of tricks.
Let’s say that you want to use VBA to control a PivotTable’s Page Field. Frankly, I could not spout off the syntax for getting the current Page value. You might not even know that there is a property burried down in the PivotTable Object that is the CurrentPage. So, how can you DISCOVER this?
- In the Code Window type in a simple sub and then STEP into it down through the Objects or Variables you want to observe. It does not need to be as specific as this. I like to use some object that I am interested in.
Sub aTest() Dim pt As PivotTable, pf As PivotField For Each pt In ActiveSheet.PivotTables For Each pf In pt.PageFields Next Next End Sub
Now I knew enough that I wanted a PageField to inspect
In the Code Window , right Click any object , object group or variable and Add Watches … - If you had selected pf in the code example, pf would appear in the Add Watch Expression: textbox . Go ahead and OK it (you can learn to manipulate the Context and Watch Type later) When you ADD a Watch, a Watches Window opens that contains a line for each Watch Expression that you have added.
If you have NO PivotTable on the Active Sheet,
pt/pfdisplays in the Watch Window as Empty.
Otherwise it will display as a…
- PivotTable/PivotField Object,
- the Value property of the PivotTable/PivotField
- the + icon that it can be opened.
Drilling down into the pf Object you find the CurrentPage, which you can see is a PivotItem Object and as such, has Properties.
Drilling down into the CurrentPage Object shows that the Caption, Name, SourceName and Value Properties
Knowing how to use this tool, opens up a new horizon of possibilities for a Power Programer.