How to use the Watch Window as a Power Programming Tool

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?

  1. 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

End Sub

Now I knew enough that I wanted a PageField to inspect

  1. 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.

  2. If you have NO PivotTable on the Active Sheet, pt/pf displays 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.
  1. Drilling down into the pf Object you find the CurrentPage, which you can see is a PivotItem Object and as such, has Properties.

  2. 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.

1 Like