AddSet Method [Excel 2003 VBA Language Reference]

Adds a new CubeField object to the CubeFields collection. The CubeField object corresponds to a set defined on the Online Analytical Processing (OLAP) provider for the cube.

expression.AddSet(Name, Caption)

expression Required. An expression that returns one of the objects in the Applies To list.

Name  Required String. A valid name in the SETS schema rowset.

Caption  Required String. A string representing the field that will be displayed in the PivotTable view.


If a set with the name given in the argument Name does not exist, the AddSet method will return a run-time error.


In this example, Microsoft Excel adds a set titled "My Set" to the CubeField object. This example assumes an OLAP PivotTable report exists on the active worksheet. Also, this example assumes a field titled "Product" exists.

Sub UseAddSet()

    Dim pvtOne As PivotTable
    Dim strAdd As String
    Dim strFormula As String
    Dim cbfOne As CubeField

    Set pvtOne = Sheet1.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

Applies to | CubeFields Collection Object