Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How to use the Watch Window as a Power Programming Tool

VBA How To

How to use the Watch Window as a Power Programming Tool

by  SkipVought  Posted    (Edited  )
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. It does not need to be as specific as this. I like to put 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

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

3. If you have NO PivotTable on the active sheet, pt/pf displays in the Watches Window as Empty. Otherwise it will display as a PivotTable/PivotField Object, the Value of which is the Value property of the PivotTable/PivotField AND it will also indicate, by virtue of the + icon that it can be opened.

4. Drilling down into the pf object you find the CurrentPage, which you can see is a PivotItem object and as such, has properties.

5. Drilling down into the CurrentPage object shows that the Caption, Name, SourceName and Value properties return the Value that is in the Current page selection.

Knowing how to use this tool, opens up a new horizon of possibilities for a Power Programer.
Register to add this FAQ to your archive
Register to rate this FAQ  : BAD 1 2 3 4 5 6 7 8 9 10 GOOD
Please Note: 1 is Bad, 10 is Good :-)

Part and Inventory Search

Back
Top