Calculated Members Collection and Calculated Member Object Example

The following routine returns information about each CalculatedMember from the data source used by the PivotTable on the active worksheet. It returns messages if either the data source is not an OLAP type or there are no CalculatedMembers:

Sub ReturnCalculatedMembers()

Dim lIcon As Long, lCount As Long

Dim ptTable As PivotTable

Dim oCalcMember As CalculatedMember

Dim oCalcMembers As CalculatedMembers

Dim sInfo As String

'Set the reference to the PivotTable

Set ptTable = ActiveSheet.PivotTables("PivotTable1")

On Error Resume Next

Set oCalcMembers = ptTable.CalculatedMembers

On Error GoTo 0

'Did we return a reference to Calculated Members?

If Not oCalcMembers Is Nothing Then

'If there's at least one Calculated Member initialize the

Count and

message

variables

If oCalcMembers.Count > 0 Then

lCount = 1

lIcon = vbInformation

'Loop through each Calculated Member and store its name and

formula

For Each oCalcMember In oCalcMembers

With oCalcMember

sInfo = sInfo & lCount & ") " & .Name

&

": " & .

.Formula

lCount = lCount + 1

End With

Next oCalcMember

Else

'It's a valid OLAP data source, but no Calculated Members

are there

lIcon = vbExclamation

sInfo = "No Calculated Members found."

End If

Else

'oCalcMembers returned nothing. Not an OLAP data source

lIcon = vbCritical

sInfo = "No Calculated Members found. Data Source

may not be OLAP type."

End If

MsgBox sInfo, lIcon, "Calculated Members"

End Sub

0 0

Post a comment