Class CalculatedFields (Excel VBA)

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

Dim cfs as CalculatedFields
Set cfs = ActiveCell.PivotTable.CalculatedFields()

For Each

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

Dim pvtfCalculatedField As PivotField
For Each pvtfCalculatedField In Application.CalculatedFields()
	
Next pvtfCalculatedField

Add

Creates a new calculated field. Returns a PivotField object.

Add (Name, Formula, UseStandardFormula)

Worksheets(1).PivotTables(1).CalculatedFields.Add "PxS", _ 
 "= Product * Sales"

Arguments

The following arguments are required:

Name (String) - The name of the field.

Formula (String) - The formula for the field.

Optional arguments

The following argument is optional

UseStandardFormula (Boolean) - False (default) for upward compatibility. True for strings contained in any arguments that are field 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.PivotTable.CalculatedFields.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 pvtfCalculatedField As PivotField
Set pvtfCalculatedField = ActiveCell.PivotTable.CalculatedFields(Index:=1)