Class CubeField (Excel VBA)

The class CubeField represents a hierarchy or measure field from an OLAP cube. In a PivotTable report, the CubeField object is a member of the CubeFields collection.

The main procedures of class CubeField are AddMemberPropertyField, Delete and CubeFields.AddSet

Set

To use a CubeField class variable it first needs to be instantiated, for example

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

The following procedures can be used to set variables of type CubeField: CubeFields.Item, CubeFields.AddSet, CubeFields.GetMeasure, PivotField.CubeField, PivotFilter.DataCubeField and PivotTable.CubeFields

For Each

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

Dim pvt As PivotTable: Set pvt = 
Dim cbfCubeField As CubeField
For Each cbfCubeField In pvt.CubeFields
	
Next cbfCubeField

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Add with its procedures AddMemberPropertyField and CubeFields.AddSet
Cube with its procedures CubeFieldSubType and CubeFieldType

Methods

These are the main methods of the CubeField class

Delete - Deletes the object.

Other Methods

AutoGroup - Automatically groups the cube fields in an OLAP cube, optionally in the specified orientation and/or at the specified position.

CubeFields.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.

Properties

AllItemsVisible the AllItemsVisible property checks whether manual filtering is applied to a PivotField or CubeField.

Caption returns a String value that represents the label text for the cube field.

CurrentPageName returns or sets the page name for a CubeField.

DragToColumn true if the specified field can be dragged to the column position. The default value is True.

DragToData true if the specified field can be dragged to the data position. The default value is True.

DragToHide true if the field can be hidden by being dragged off the PivotTable report. The default value is True.

DragToPage true if the field can be dragged to the page position. The default value is True.

DragToRow true if the field can be dragged to the row position. The default value is True.

EnableMultiplePageItems set to True to allow multiple items in the page field area for OLAP PivotTables to be selected. The default value is False.

Sub UseMultiplePageItems() 
 
 Dim pvtTable As PivotTable 
 Dim cbeField As CubeField 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set cbeField = pvtTable.CubeFields("[Country]") 
 
 ' Determine setting for multiple page items. 
 If cbeField.EnableMultiplePageItems = False Then 
 MsgBox "Multiple page items cannot be selected." 
 Else 
 MsgBox "Multiple page items can be selected." 
 End If 
End Sub

FlattenHierarchies returns or sets whether items from all levels of hierarchies in a named set cube field are displayed in the same field of a PivotTable report based on an OLAP cube.

ActiveSheet.PivotTables("PivotTable1").CubeFields("[Summary P&L]"). _ 
 FlattenHierarchies = True

HasMemberProperties returns True when there are member properties specified to be displayed for the cube field.

Sub UseHasMemberProperties() 
 
 Dim pvtTable As PivotTable 
 Dim cbeField As CubeField 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set cbeField = pvtTable.CubeFields("[Country]") 
 
 ' Determine if there are member properties to be displayed. 
 If cbeField.HasMemberProperties = True Then 
 MsgBox "There are member properties to be displayed." 
 Else 
 MsgBox "There are no member properties to be displayed." 
 End If 
 
End Sub

HierarchizeDistinct returns or sets whether to order and remove duplicates when displaying the specified named set in a PivotTable report based on an OLAP cube.

ActiveSheet.PivotTables("PivotTable1").CubeFields("[Summary P&L]"). _ 
 HierarchizeDistinct = True

IncludeNewItemsInFilter the IncludeNewItemsInFilter property is used to track included/excluded items in OLAP PivotTables.

IsDate returns True if the CubeField is a date.

LayoutForm returns or sets the way the specified PivotTable items appear—in table format or in outline format.

With ActiveSheet.PivotTables("PivotTable1") _ 
 .PivotFields("state") 
 .LayoutForm = xlOutline 
 .LayoutSubtotalLocation = xlTop 
End With

LayoutSubtotalLocation returns or sets the position of the PivotTable field subtotals in relation to (either above or below) the specified field.

With ActiveSheet.PivotTables("PivotTable1") _ 
 .PivotFields("state") 
 .LayoutForm = xlOutline 
 .LayoutSubtotalLocation = xlAtTop 
End With

Name returns a String value that represents the name of the object.

Orientation returns or sets an XlPivotFieldOrientation value that represents the location of the field in the specified PivotTable report.

Parent returns the parent object for the specified object. Read-only.

PivotFields returns the PivotFields collection. This collection contains all PivotTable fields, including those that aren't currently visible on-screen.

Set objNewSheet = Worksheets.Add 
objNewSheet.Activate 
intRow = 1 
For Each objPF In _ 
 Charts("Chart1").PivotLayout.PivotFields 
 objNewSheet.Cells(intRow, 1).Value = objPF.Caption 
 intRow = intRow + 1 
Next objPF

Position returns or sets a Long value that represents the position of the hierarchy field on the PivotTable report when it's dragged from the field well.

ShowInFieldList when set to True (default), a CubeField object will be shown in the field list.

Sub IsCubeFieldInList() 
 
 Dim pvtTable As PivotTable 
 Dim cbeField As CubeField 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set cbeField = pvtTable.CubeFields("[Country]") 
 
 ' Determine if a CubeField can be seen. 
 If cbeField.ShowInFieldList = True Then 
 MsgBox "The CubeField object can be seen in the field list." 
 Else 
 MsgBox "The CubeField object cannot be seen in the field list." 
 End If 
 
End Sub

TreeviewControl returns the TreeviewControl object of the CubeField object, representing the cube manipulation control of an OLAP-based PivotTable report.

ActiveSheet.PivotTables("PivotTable2") _ 
 .CubeFields(1).TreeviewControl.Drilled = _ 
 Array(Array("", ""), _ 
 Array("[state].[states].[CA]", _ 
 "[state].[states].[MD]"))

Value returns a String value that represents the name of the specified field.

CubeFields.Count returns a Long value that represents the number of objects in the collection.

CubeFields.Item returns a single object from a collection.

blnFoundName = False 
For Each objPT in ActiveSheet.PivotTables 
 Set objCubeField = _ 
 objPT.CubeFields.Item(1) 
 If instr(1,objCubeField.Name, "Paris") <> 0 Then 
 blnFoundName = True 
 Exit For 
 End If 
Next objPT

CubeFields.Parent returns the parent object for the specified object. Read-only.