Class CalculatedItems (Excel VBA)

A collection of PivotItem objects that represents all the calculated items in the specified PivotTable report. To use a CalculatedItems class variable it first needs to be instantiated, for example

Dim cis as CalculatedItems
Set cis = ActiveCell.PivotField.CalculatedItems()

For Each

Here is an example of processing the CalculatedItems items in a collection.

Dim pvtiCalculatedItem As PivotItem
For Each pvtiCalculatedItem In Application.CalculatedItems()
	
Next pvtiCalculatedItem

Add

Creates a new calculated item. Returns a PivotItem object.

Add (Name, Formula, UseStandardFormula)

Dim strName As String: strName = 
Dim strFormula As String: strFormula = 
Dim pvti As PivotItem
Set pvti = ActiveCell.PivotField.CalculatedItems.Add(Name:=strName, Formula:=strFormula)

Arguments

The following arguments are required:

Name (String) - The name of the item.

Formula (String) - The formula for the item.

Optional arguments

The following argument is optional

UseStandardFormula (Boolean) - False (default) for upward compatibility. True for strings contained in any arguments that are item names; will be interpreted as having been formatted in standard U.S. English instead of local settings.

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveCell.PivotField.CalculatedItems.Count

Item

Returns a single object from a collection.

The text name of the object is the value of the Name and Value properties.

Item (Index)

Index: The name or index number for the object.

Dim pvtiCalculatedItem As PivotItem
Set pvtiCalculatedItem = ActiveCell.PivotField.CalculatedItems(Index:=1)