Skip to content

Latest commit

 

History

History
86 lines (55 loc) · 2.84 KB

Excel.CalculatedMembers.Add.md

File metadata and controls

86 lines (55 loc) · 2.84 KB
title keywords f1_keywords api_name ms.assetid ms.date ms.localizationpriority
CalculatedMembers.Add method (Excel)
vbaxl10.chm684078
vbaxl10.chm684078
Excel.CalculatedMembers.Add
8c6591bb-3906-6682-4dc7-89ffc2ae74f3
04/13/2019
medium

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

[!includeSupport and feedback]