Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

VBA Visual Basic for Applications (Microsoft) FAQ

VBA How To

How to use the Watch Window as a Power Programming Tool by SkipVought
Posted: 16 Dec 03 (Edited 16 Dec 03)

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.

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

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.

Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close