Class PivotFormulas (Excel VBA)

The class PivotFormulas represents the collection of formulas for a PivotTable report. Each formula is represented by a PivotFormula object. To use a PivotFormulas class variable it first needs to be instantiated, for example

Dim pfs as PivotFormulas
Set pfs = ActiveCell.PivotTable.PivotFormulas

For Each

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

Dim pvtfor As PivotFormula
For Each pvtfor In ActiveCell.PivotTable.PivotFormulas
	
Next pvtfor

Add

Creates a new PivotTable formula.

Add (Formula, UseStandardFormula)

Worksheets(1).PivotTables(1).PivotFormulas _ 
 .Add "Year['1998'] Apples = (Year['1997'] Apples) * 2"

Arguments

The following argument is required

Formula (String) - The new PivotTable formula.

Optional arguments

The following argument is optional

UseStandardFormula - A standard PivotTable formula

Count

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

Dim lngCount As Long
lngCount = ActiveCell.PivotTable.PivotFormulas.Count

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number for the object.

Dim pvtfor As PivotFormula
Set pvtfor = ActiveCell.PivotTable.PivotFormulas(Index:=1)