Class CalculatedMember (Excel VBA)

The class CalculatedMember represents the calculated fields, calculated items, and named sets for PivotTables with Online Analytical Processing (OLAP) data sources.

The main procedures of class CalculatedMember are Delete, CalculatedMembers.Add and CalculatedMembers.AddCalculatedMember

Set

To use a CalculatedMember class variable it first needs to be instantiated, for example

Dim strName As String: strName = 
Dim strFormula As String: strFormula = 
Dim mcn As ModelConnection: Set mcn = 
Dim clcm As CalculatedMember
Set clcm = mcn.CalculatedMembers.Add(Name:=strName, Formula:=strFormula, DisplayFolder:="")

The following procedures can be used to set variables of type CalculatedMember: CalculatedMembers.Item, CalculatedMembers.Add, CalculatedMembers.AddCalculatedMember, ModelConnection.CalculatedMembers, OLEDBConnection.CalculatedMembers and PivotTable.CalculatedMembers

For Each

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

Dim mcn As ModelConnection: Set mcn = 
Dim clcmCalculatedMember As CalculatedMember
For Each clcmCalculatedMember In mcn.CalculatedMembers
	
Next clcmCalculatedMember

Themes

The following procedures in this class have been grouped together and are described on a separate theme page

Add with its procedures CalculatedMembers.Add and CalculatedMembers.AddCalculatedMember

Methods

These are the main methods of the CalculatedMember class

Delete - Deletes the object.

Properties

DisplayFolder returns the display folder name for a named set.

Dynamic returns whether the specified named set is recalculated with every update.

FlattenHierarchies returns or sets whether items from all levels of the hierarchy of the specified named set are displayed in the same field of a PivotTable report based on an OLAP cube.

Formula returns a String value that represents the member's formula in multidimensional expressions (MDX) syntax.

HierarchizeDistinct returns or sets whether to order and remove duplicates when displaying the hierarchy of the specified named set in a PivotTable report based on an OLAP cube.

IsValid returns a Boolean that indicates whether the specified calculated member has been successfully instantiated with the OLAP provider during the current session.

Sub CheckValidity() 
 
 Dim pvtTable As PivotTable 
 Dim pvtCache As PivotCache 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1) 
 
 ' Make connection for PivotTable before testing IsValid property. 
 pvtCache.MakeConnection 
 
 ' Check if calculated member is valid. 
 If pvtTable.CalculatedMembers.Item(1).IsValid = True Then 
 MsgBox "The calculated member is valid." 
 Else 
 MsgBox "The calculated member is not valid." 
 End If 
 
End Sub

MeasureGroup returns the associated measure group.

Name returns a String value that represents the name of the object.

NumberFormat returns an XlCalcMemNumberFormatType value that represents the number format of the calculated member. The default value is xlNumberFormatTypeDefault.

Parent returns the parent object for the specified object. Read-only.

ParentHierarchy returns the name of the current parent hierarchy from the hierarchies that are available on the cube.

ParentMember returns the name of the parent member for the parent hierarchy.

SolveOrder returns a Long specifying the value of the calculated member's solve order MDX (Mulitdimensional Expression) argument. The default value is zero.

Sub CheckSolveOrder() 
 
 Dim pvtTable As PivotTable 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 
 ' Determine solve order and notify user. 
 If pvtTable.CalculatedMembers.Item(1).SolveOrder = 0 Then 
 MsgBox "The solve order is set to the default value." 
 Else 
 MsgBox "The solve order is not set to the default value." 
 End If 
 
End Sub

SourceName returns a String value that represents the specified object's name as it appears in the original source data for the specified PivotTable report.

Type returns an XlCalculatedMemberType value that represents the calculated member type.

CalculatedMembers.Count returns a Long value that represents the number of objects in the collection.

CalculatedMembers.Item returns a single object from a collection.

Sub CheckValidity() 
 
 Dim pvtTable As PivotTable 
 Dim pvtCache As PivotCache 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set pvtCache = Application.ActiveWorkbook.PivotCaches.Item(1) 
 
 ' Handle run-time error if external source is not an OLEDB data source. 
 On Error GoTo Not_OLEDB 
 
 ' Check connection setting and make connection if necessary. 
 If pvtCache.IsConnected = False Then 
 pvtCache.MakeConnection 
 End If 
 
 ' Check if calculated member is valid. 
 If pvtTable.CalculatedMembers.Item(1).IsValid = True Then 
 MsgBox "The calculated member is valid." 
 Else 
 MsgBox "The calculated member is not valid." 
 End If 
 
Not_OLEDB: 
MsgBox "The source is not an OLEDB data source." 
Exit Sub 
 
End Sub

CalculatedMembers.Parent returns the parent object for the specified object. Read-only.