Pivot Cell and Pivot ItemList Objects

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

?c PivotHeid m ?Q Pivotitam

- 7U PivotltfimList

+1 ?PivotTabie

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):

• Columnltems

• CustomSubtotalFunction

• PivotCellType

• PivotField

• PivotTable

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 Columnltems property generates an error, rather than returning an empty collection, for example.

+2 0

Post a comment