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

• Add
• AddCalculatedMember

Properties

• Application
• Count
• Creator
• Item
• Parent

See also

• Excel Object Model Reference

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.