How to use the Watch Window as a Power Programming Tool

Engineering Software general discussion VBA
, , , ,
#1

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.

CODE

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

  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