CubeFields.AddSet method (Excel)

Artikkeli
09/13/2021

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.

Syntax

expression.AddSet (Name, Caption)

expression A variable that represents a CubeFields object.

Parameters

Name	Required/Optional	Data type	Description
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.

Return value

CubeField

Remarks

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

Example

In this example, Microsoft Excel adds a set titled My Set to the CubeField object. This example assumes that an OLAP PivotTable report exists on the active worksheet, and that 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

Support and feedback

Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.

Jaa