Microsoft Office Tutorials and References
In Depth Information
20.11 Calculated Items and Calculated Fields
The PivotTable property returns the PivotTable object associated with the PivotCell. The
PivotField property returns the PivotField object associated with the PivotCell; and the PivotItem
property returns the PivotItem associated with the PivotCell. Referring to Figure 20-6 , the
following code illustrates these properties:
Sub test()
' Refer to Figure 20-6
Dim rng As Range
Dim ws As Worksheet
Set ws = ActiveSheet
Set rng = ws.Range("E5")
Debug.Print rng.PivotCell.PivotTable.Name ' returns Sales&Trans
Debug.Print rng.PivotCell.PivotField ' returns Sum of
Transactions
Set rng = ws.Range("B5")
Debug.Print rng.PivotCell.PivotItem.Name ' returns Company
End Sub
20.11 Calculated Items and Calculated Fields
We have seen that it is possible to add a calculated field to a pivot table. A calculated field is
special type of PivotField object that is not part of the original source data, but, instead, is
calculated from source fields using a formula. Note that there is no such thing as a CalculatedField
object, but there is a CalculatedFields collection.
As we have seen, to create a new calculated field, we use the Add method of the CalculatedFields
collection of the PivotTable object. The syntax is:
CalculatedFieldsObject .Add( Name , Formula )
where Name is the name of the field and Formula is the formula for the field.
On the other hand, a calculated item is a special type of PivotItem object associated with a given
PivotField object. (There is no such thing as a CalculatedItem object, but there is a
CalculatedItems collection.) The values of this item are calculated by using a formula.
The PivotField object has a CalculatedItems collection of all calculated items for that pivot field.
To create a new calculated item, we use the Add method of the CalculatedItems object. This
method has the same syntax as the Add method of the CalculatedFields object:
CalculatedItemsObject .Add( Name , Formula )
where Name is the name of the field and Formula is the formula for the field.
To illustrate, the following code adds a new calculated item to the Store Type pivot field:
ActiveSheet.PivotTables("Sales&Trans"). _
PivotFields("Store Type").CalculatedItems. _
 
Search JabSto ::




Custom Search