Programming Excel with VBA and .NET

PivotFormula and PivotFormulas Members

Use the PivotFormulas collection to get the formulas of calculated pivot items. Use the PivotTable object’s PivotFormulas property to get a reference to this collection. Use the PivotFormula object to get information about the calculated item. The PivotFormulas collection and PivotFormula object have the following members:

1 Collection only
2 Object and collection
Add 1	Application 2
Count 1	Creator 2
Delete	Formula
Index	Item 1
Parent 2	StandardFormula
Value

The following code displays the formulas of calculated items in a pivot table:

Sub GetPivotFormula(  )
    Dim pt As PivotTable, pfa As PivotFormula
    ' Uncomment next line to add a calculated pivot item.
    'NewCalcItem
    ' Get pivot table.
    Set pt = Worksheets("BookSales").PivotTables(1)
    ' Show formulas for calculated items.
    For Each pfa In pt.PivotFormulas
        Debug.Print pfa.Value
    Next
End Sub

The PivotFormulas collection doesn’t include formulas from calculated pivot fields. Use the CalculatedFields collection to get those formulas.