Class CubeFields (Excel VBA)

A collection of all CubeField objects in a PivotTable report that is based on an OLAP cube. Each CubeField object represents a hierarchy or measure field from the cube. To use a CubeFields class variable it first needs to be instantiated, for example

Dim cfs as CubeFields
Set cfs = ActiveCell.PivotTable.CubeFields

For Each

Here is an example of processing the CubeFields items in a collection.

Dim cbf As CubeField
For Each cbf In ActiveCell.CubeFields
	
Next cbf

AddSet

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.

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

AddSet (Name, Caption)

Dim strName As String: strName = 
Dim strCaption As String: strCaption = 
Dim cbfAddSet As CubeField
Set cbfAddSet = ActiveCell.PivotTable.CubeFields.AddSet(Name:=strName, Caption:=strCaption)

Arguments

The following arguments are required:

Name (String) - A valid name in the SETS schema rowset.

Caption (String) - A string representing the field that will be displayed in the PivotTable view.

Count

Returns a Long value that represents the number of objects in the collection.

Dim lngCount As Long
lngCount = ActiveCell.PivotTable.CubeFields.Count

GetMeasure

Given an attribute hierarchy, returns an implicit measure for the given function that corresponds to this attribute. If an implicit measure does not exist, a new implicit measure is created and added to the CubeFields collection.

Getting a measure by using the GetMeasure function will work for these functions only: Count, Sum, Average, Max, and Min. For example, these will work:

GetMeasure (AttributeHierarchy, Function, Caption)

Dim cbfGetMeasure As CubeField
Set cbfGetMeasure = ActiveCell.PivotTable.CubeFields.GetMeasure(AttributeHierarchy:=xlHierarchy, Function:=xlAverage)

Arguments

The following arguments are required:

AttributeHierarchy (XlCubeFieldSubType) - The unique cube field that is an attribute hierarchy (XlCubeFieldType = xlHierarchy, and XlCubeFieldSubType = xlCubeAttribute).

Here you can find possible values for XlCubeFieldSubType

Function (XlConsolidationFunction) - The function performed in the added data field.

Here you can find possible values for XlConsolidationFunction

Optional arguments

The following argument is optional

Caption (String) - The label used in the PivotTable report to identify this measure. If the measure already exists, Caption will overwrite the existing label of this measure.

Item

Returns a single object from a collection.

Item (Index)

Index: The name or index number of the object.

Dim cbfItem As CubeField
Set cbfItem = ActiveCell.PivotTable.CubeFields(Index:=1)