CalculatedMembers.Add Method

  • Article
Excel Developer Reference

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

Syntax

expression.Add(Name, Formula, SolveOrder, Type)

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.

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, then you must call the AddSet method to make the new field set visible in the PivotTable.

Example

The following example adds a set to a PivotTable.

Bb211601.vs_note(en-us,office.12).gif  Note
Connection to the cube and existing pivot table is necessary for the sample to run.
Visual Basic for Applications 
  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.<strong>Add</strong> 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

See Also