| title | keywords | f1_keywords | api_name | ms.assetid | ms.date | ms.localizationpriority | ||
|---|---|---|---|---|---|---|---|---|
CalculatedMembers object (Excel) |
vbaxl10.chm683072 |
|
|
3c664ac6-e2f8-f631-006d-6a16c380641e |
03/29/2019 |
medium |
A collection of all the CalculatedMember objects on the specified PivotTable.
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
-
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 SubNote
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.
[!includeSupport and feedback]