Excel.CubeFields.AddSet.md

title	keywords	f1_keywords	api_name	ms.assetid	ms.date	ms.localizationpriority
CubeFields.AddSet method (Excel)	vbaxl10.chm670077	vbaxl10.chm670077	Excel.CubeFields.AddSet	2f40d4f3-56fc-4d98-b214-623885dc26d6	04/23/2019	medium

CubeFields.AddSet method (Excel)

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

[!includeSupport and feedback]