Show All

CalculatedMembers Property

Returns a CalculatedMembers collection representing all the calculated fields and calculated items for an OLAP PivotTable.

expression   Required. An expression that returns a PivotTable object.

Remarks

This property is used for Online Analytical Processing (OLAP) sources; a non-OLAP source will return a run-time error.

Example

This example adds a set to the PivotTable. It assumes a PivotTable exists on the active worksheet that is connected to an OLAP data source which contains a field titled "[Product].[All Products]".

Sub UseCalculatedMember()

    Dim pvtTable As PivotTable

    Set pvtTable = ActiveSheet.PivotTables(1)

    ' Add the calculated member.
    pvtTable.CalculatedMembers.Add Name:="[Beef]", _
        Formula:="'{[Product].[All Products].Children}'", _
        Type:=xlCalculatedSet

End Sub