Microsoft Office 2000/Visual Basic Programmer's Guide

Working with PivotTable Fields

The PivotFields collection contains all of the fields available in the PivotTable's data source. If you need to refer to a PivotTable field in a particular area of a PivotTable report, it's often easier to use one of the properties in the following table, which define subsets of the PivotFields collection.

For example, in order to determine whether a particular field is visible in a PivotTable report, you need to determine whether it is in the subset of visible fields. The following code fragment checks whether the City and Country fields are currently visible in a PivotTable report by first suppressing error handling, and then attempting to return a reference to each field within the VisibleFields subset of the PivotFields collection. If no error occurs, the field is visible and the check box is set to True. If an error occurs, the field is not visible and the check box is set to False.

' Determine whether City and Country fields
' are visible by attempting to return a
' reference to them and checking for the error.
With p_pvtTable
   ' The VisibleFields property returns the
   ' collection of fields visible in the 
   ' PivotTable report.
   On Error Resume Next
   Set pvfCity = .VisibleFields("City")
   If Err = 0 Then chkByCity = True
   Err.Clear
   Set pvfCountry = .VisibleFields("Country")
   If Err = 0 Then chkByCountry = True
   Err.Clear
   .
   .
   .
End With

This code fragment is taken from the UserForm_Initialize event procedure for the frmPivotOptions form in the Northwind.xls sample file, which is available in the ODETools\V9\Sampes\OPG\Samples\CH15 subfolder on the Office 2000 Developer CD-ROM.