CalculatedMembers object (Excel)
A collection of all the CalculatedMember objects on the specified PivotTable.
Remarks
Each CalculatedMember object represents a calculated member or calculated measure.
Use the CalculatedMembers property of the PivotTable object to return a CalculatedMembers collection.
There are three supported types of calculated members: Named Sets, Calculated Members, and Calculated Measures. Object model support has been available for all three types since Excel 2010. User interface support was made available for Named Sets in Excel 2010. In Excel 2013, the OLAP Calculated Members and Calculated Measures feature was created to build a user interface for the calculated members and measures object model.
Named Sets is used exactly the same as in Excel 2010. Named Sets should continue to use the Add method, and the type XlCalculatedMemberType enumeration.
Calculated Members has the following changes for Excel 2013:
It now uses the AddCalculatedMember method.
It supports the following properties of the CalculatedMember object:
ParentHierarchy property
ParentMember property
NumberFormat property
Calculated Measures has the following changes for Excel 2013:
It now uses the AddCalculatedMember method.
It now uses the type XlCalculatedMemberType enumeration.
It supports the following properties of the CalculatedMember object:
DisplayFolder property
NumberFormat property
Example
The following example adds a set to a PivotTable, assuming that a PivotTable from the FoodMart SQL database exists on the active worksheet.
Sub UseCalculatedMember()
Dim pvtTable As PivotTable
Set pvtTable = ActiveSheet.PivotTables(1)
pvtTable.CalculatedMembers.Add Name:="[Beef]", _
Formula:="'{[Product].[All Products].Children}'", _
Type:=xlCalculatedSet
End Sub
Note
For the Add method in the previous example, the Formula argument must have a valid MDX syntax statement. The Name argument has to be acceptable to the Online Analytical Processing (OLAP) provider and the Type argument has to be defined.
Methods
Properties
See also
Support and feedback
Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.