Class CalculatedMembers (Excel VBA)

A collection of all the CalculatedMember objects on the specified PivotTable. To use a CalculatedMembers class variable it first needs to be instantiated, for example

Dim cms as CalculatedMembers
Set cms = ActiveCell.PivotTable.CalculatedMembers

For Each

Here is an example of processing the CalculatedMembers items in a collection.

Dim clcm As CalculatedMember
For Each clcm In ActiveCell.PivotTable.CalculatedMembers
	
Next clcm

Add

Adds a calculated field or calculated item to a PivotTable. Returns a CalculatedMember object.

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.

Add (Name, Formula, SolveOrder, Type, Dynamic, DisplayFolder, HierarchizeDistinct)

Dim strName As String: strName = 
Dim strFormula As String: strFormula = 
Dim clcm As CalculatedMember
Set clcm = ActiveCell.PivotTable.CalculatedMembers.Add(Name:=strName, Formula:=strFormula)

Arguments

The following arguments are required:

Name (String) - The name of the calculated member.

Formula (String) - The formula of the calculated member.

Optional arguments

The following arguments are optional

SolveOrder - The solve order for the calculated member

Type (XlCalculatedMemberType) - The type of calculated member.

Possible return values are xlCalculatedMeasure - The member is a Multidimensional Expressions (MDX) expression that defines the measure, xlCalculatedMember - The member uses a Multidimensional Expression (MDX) formula, xlCalculatedSet - The member contains an MDX formula for a set in a cube field.

Dynamic (Boolean) - Specifies if the calculated member is recalculated with every update.

DisplayFolder (String) - The name of the display folder for the calculated member.

HierarchizeDistinct (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.

AddCalculatedMember

Adds a calculated field or calculated item to a PivotTable.

The Formula argument must have a valid MDX (multidimensional expression) syntax statement. The Name argument has to be acceptable to the Online Analytical Processing (OLAP) provider.

AddCalculatedMember (Name, Formula, SolveOrder, Type, DisplayFolder, MeasureGroup, ParentHierarchy, ParentMember, NumberFormat)

OLEDBConnection.CalculatedMembers.AddCalculatedMember Name:="[UK+US]", _
     Formula:= _
    "[Customer].[Customer Geography].[Country].&[United Kingdom] + [Customer].[Customer Geography].[Country].&[United States] " _
     , Type:=xlCalculatedMember, SolveOrder:=0, ParentHierarchy:= _
     "[Account].[Accounts]", ParentMember:= _
    "[Customer].[Customer Geography].[Australia]", NumberFormat:= _
     xlNumberFormatTypePercent

Arguments

The following arguments are required:

Name (String) - The name of the calculated member.

Formula (String) - The formula of the calculated member.

Optional arguments

The following arguments are optional

SolveOrder - The solve order for the calculated member

Type (XlCalculatedMemberType) - The type of calculated member.

Possible return values are xlCalculatedMeasure - The member is a Multidimensional Expressions (MDX) expression that defines the measure, xlCalculatedMember - The member uses a Multidimensional Expression (MDX) formula, xlCalculatedSet - The member contains an MDX formula for a set in a cube field.

DisplayFolder (String) - A folder that exists to display calculated measures.

MeasureGroup (String) - The group to which the calculated member belongs.

ParentHierarchy (String) - The parent path of the ParentMember.

ParentMember (Object) - The parent of the calculated member.

NumberFormat (XlCalcMemNumberFormatType) - The format of numbers used for calculated members.

Possible return values are xlNumberFormatTypeDefault - Use the default format type of the calculated member for the cell value, xlNumberFormatTypeNumber - Calculated member cell format is a number, xlNumberFormatTypePercent - Calculated member cell format is a percentage.

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveCell.PivotTable.CalculatedMembers.Count

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number of the object.

Dim clcmItem As CalculatedMember
Set clcmItem = ActiveCell.PivotTable.CalculatedMembers(Index:=1)