Microsoft Office Tutorials and References
In Depth Information
Figure 20-40. The PivotCell object
New to Excel XP is the PivotCell object, which represents a cell in a pivot table. This object and
its children are shown in Figure 20-40 .
Figure 20-40. The PivotCell object
To obtain a PivotCell object, we use the PivotCell property of the Range object, as in:
MsgBox ActiveSheet.Range("C5").PivotCell.PivotCellType
Note that if the range contains more than one cell, the PivotCell object for the first cell is returned.
The PivotCell object has no methods. Among its 13 properties, here are the most important (and
the ones that seem to work):
ColumnItems
CustomSubtotalFunction
DataField
PivotCellType
PivotField
PivotItem
PivotTable
RowItems
The following sections provide a brief description of these properties. It is important to keep in
mind that Excel is not at all friendly when it comes to applying these properties to a PivotCell
object. That is, if the PivotCell object is not of a type that supports the property, then Excel will
return an error. For instance, if a cell is not a data cell in a pivot table, then accessing the PivotCell
object's ColumnItems property generates an error, rather than returning an empty collection, for
example.
20.10.1 ColumnItems, RowItems, and DataField Properties
The ColumnItems and RowItems properties each return a PivotItemList collection that contains
the column (or row) pivot items associated to the PivotCell. The DataField property returns a
single PivotField item associated to the PivotCell. Note that these properties will generate an error
(with the usual unhelpful message) if applied to the PivotCell object of a cell that is not an
aggregate (data) cell in the pivot table.
To illustrate, consider the pivot table in Figure 20-6 . The PivotItemList procedure shown here
displays the column pivot items, row pivot, items, and data field associated with the PivotCell for
the range (cell) E5.
Sub PivotItemList()
' Illustrate the ColumnItems and RowItems properties
Dim rng As Range
Dim pi As PivotItem
 
Search JabSto ::




Custom Search