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




Item 1

Parent 2




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.
    ' Get pivot table.
    Set pt = Worksheets("BookSales").PivotTables(1)
    ' Show formulas for calculated items.
    For Each pfa In pt.PivotFormulas
        Debug.Print pfa.Value
End Sub

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

Get Programming Excel with VBA and .NET now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.