CalculatedMembers.Add method (Excel)
Adds a calculated field or calculated item to a PivotTable. Returns a CalculatedMember object.
Syntax
expression.Add (Name, Formula, SolveOrder, Type, Dynamic, DisplayFolder, HierarchizeDistinct)
expression A variable that represents a CalculatedMembers object.
Parameters
| Name | Required/Optional | Data type | Description |
|---|---|---|---|
| Name | Required | String | The name of the calculated member. |
| Formula | Required | String | The formula of the calculated member. |
| SolveOrder | Optional | Variant | The solve order for the calculated member. |
| Type | Optional | Variant | The type of calculated member. |
| Dynamic | Optional | Boolean | Specifies if the calculated member is recalculated with every update. |
| DisplayFolder | Optional | String | The name of the display folder for the calculated member. |
| HierarchizeDistinct | Optional | Boolean | Specifies whether to order and remove duplicates when displaying the hierarchy of the calculated member in a PivotTable report based on an OLAP cube. |
Return value
A CalculatedMember object that represents the new calculated field or calculated item.
Remarks
The Formula argument must contain a valid MDX (Multidimensional Expression) syntax statement. The Name argument has to be acceptable to the Online Analytical Processing (OLAP) provider, and the Type argument has to be defined.
If you set the Type argument of this method to xlCalculatedSet, you must call the AddSet method of the CubeFields object to make the new field set visible in the PivotTable.
Example
The following example adds a set to a PivotTable.
Note
Connection to the cube and existing PivotTable is necessary for the sample to run.
Sub UseAddSet()
Dim pvtOne As PivotTable
Dim strAdd As String
Dim strFormula As String
Dim cbfOne As CubeField
Set pvtOne = ActiveSheet.PivotTables(1)
strAdd = "[MySet]"
strFormula = "'{[Product].[All Products].[Food].children}'"
' Establish connection with data source if necessary.
If Not pvtOne.PivotCache.IsConnected Then pvtOne.PivotCache.MakeConnection
' Add a calculated member titled "[MySet]"
pvtOne.CalculatedMembers.Add Name:=strAdd, _
Formula:=strFormula, Type:=xlCalculatedSet
' Add a set to the CubeField object.
Set cbfOne = pvtOne.CubeFields.AddSet(Name:="[MySet]", _
Caption:="My Set")
End Sub
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.