Class PivotField (Excel VBA)

The class PivotField represents a field in a PivotTable report.

The main procedures of class PivotField are CalculatedFields.Add, AddPageItem and Delete

Set

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

Dim strName As String: strName = 
Dim strFormula As String: strFormula = 
Dim pvt As PivotTable: Set pvt = 
Dim pvtf As PivotField
Set pvtf = pvt.CalculatedFields.Add(Name:=strName, Formula:=strFormula)

The following procedures can be used to set variables of type PivotField: PivotTable.CalculatedFields, CalculatedFields.Item, CalculatedFields.Add, PivotCell.DataField, PivotCell.PivotField, ChildField, ParentField, PropertyParentField, PivotFields.Item, PivotFilter.DataField, PivotFilter.PivotField, PivotFilter.MemberPropertyField, PivotItem.Parent, PivotItems.Parent, PivotTable.AddDataField, PivotTable.ColumnFields, PivotTable.DataFields, PivotTable.DataPivotField, PivotTable.HiddenFields, PivotTable.PageFields, PivotTable.RowFields, PivotTable.VisibleFields, Range.PivotField, CubeField.PivotFields, PivotTable.PivotFields, PivotTable.ColumnFields, PivotTable.DataFields, PivotTable.HiddenFields, PivotTable.PageFields, PivotTable.RowFields and PivotTable.VisibleFields

For Each

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

Dim pvt As PivotTable: Set pvt = 
Dim pvtfCalculatedField As PivotField
For Each pvtfCalculatedField In pvt.CalculatedFields()
	
Next pvtfCalculatedField

Themes

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

Sort/Order with its procedures AutoSort, AutoSortCustomSubtotal, AutoSortField, AutoSortOrder, AutoSortPivotLine, DatabaseSort and PropertyOrder
Display/Show with its procedures DisplayAsCaption, DisplayAsTooltip, DisplayInReport, ShowAllItems, ShowDetail and ShowingInAxis
Formula with its procedures Formula and StandardFormula
Name with its procedures MemberPropertyCaption, SourceCaption, SourceName, SubtotalName and UseMemberPropertyAsCaption

Methods

These are the main methods of the PivotField class

CalculatedFields.Add - Creates a new calculated field. Returns a PivotField object.

Worksheets(1).PivotTables(1).CalculatedFields.Add "PxS", _ 
 "= Product * Sales"

AddPageItem - Adds an additional item to a multiple item page field.

Sub UseAddPageItem() 
 
 ' The source is an OLAP database and you can manually reorder items. 
 ActiveSheet.PivotTables(1).CubeFields("[Product]"). _ 
 EnableMultiplePageItems = True 
 
 ' Add the page item titled "[Product].[All Products].[Food].[Eggs]". 
 ActiveSheet.PivotTables(1).PivotFields("[Product]").AddPageItem ( _ 
 "[Product].[All Products].[Food].[Eggs]") 
 
End Sub

Delete - Deletes the object.

Other Methods

AutoShow - Displays the number of top or bottom items for a row, page, or column field in the specified PivotTable report.

ActiveSheet.PivotTables("Pivot1").PivotFields("Company") _ 
 .AutoShow xlAutomatic, xlTop, 2, "Sum of Sales"

Properties

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

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

AllItemsVisible used to retrieve a Boolean value that indicates whether any manual filtering is applied to the PivotField.

AutoShowCount returns the number of top or bottom items that are automatically shown in the specified PivotTable field.

With Worksheets(1).PivotTables(1).PivotFields("salesman") 
 If .AutoShowType = xlAutomatic Then 
 r = .AutoShowRange 
 If r = xlTop Then 
 rn = "top" 
 Else 
 rn = "bottom" 
 End If 
 MsgBox "PivotTable report is showing " & rn & " " & _ 
 .AutoShowCount & " items in " & .Name & _ 
 " field by " & .AutoShowField 
 Else 
 MsgBox "PivotTable report is not using AutoShow for this field" 
 End If 
End With

AutoShowField returns the name of the data field used to determine the top or bottom items that are automatically shown in the specified PivotTable field.

With Worksheets(1).PivotTables(1).PivotFields("salesman") 
 If .AutoShowType = xlAutomatic Then 
 r = .AutoShowRange 
 If r = xlTop Then 
 rn = "top" 
 Else 
 rn = "bottom" 
 End If 
 MsgBox "PivotTable report is showing " & rn & " " & _ 
 .AutoShowCount & " items in " & .Name & _ 
 " field by " & .AutoShowField 
 Else 
 MsgBox "PivotTable report is not using AutoShow for this field" 
 End If 
End With

AutoShowRange returns the xlTop constant if the top items are shown automatically in the specified PivotTable field; returns xlBottom if the bottom items are shown.

With Worksheets(1).PivotTables(1).PivotFields("salesman") 
 If .AutoShowType = xlAutomatic Then 
 r = .AutoShowRange 
 If r = xlTop Then 
 rn = "top" 
 Else 
 rn = "bottom" 
 End If 
 MsgBox "PivotTable report is showing " & rn & " " & _ 
 .AutoShowCount & " items in " & .Name & _ 
 " field by " & .AutoShowField 
 Else 
 MsgBox "PivotTable report is not using AutoShow for this field" 
 End If 
End With

AutoShowType returns the xlAutomatic constant if AutoShow is enabled for the specified PivotTable field; returns xlManual if AutoShow is disabled.

With Worksheets(1).PivotTables(1).PivotFields("salesman") 
 If .AutoShowType = xlAutomatic Then 
 r = .AutoShowRange 
 If r = xlTop Then 
 rn = "top" 
 Else 
 rn = "bottom" 
 End If 
 MsgBox "PivotTable report is showing " & rn & " " & _ 
 .AutoShowCount & " items in " & .Name & _ 
 " field by " & .AutoShowField 
 Else 
 MsgBox "PivotTable report is not using AutoShow for this field" 
 End If 
End With

BaseField returns or sets the base field for a custom calculation. This property is valid only for data fields.

With Worksheets("Sheet1").Range("A3").PivotField 
 .Calculation = xlDifferenceFrom 
 .BaseField = "ORDER_DATE" 
 .BaseItem = "5/16/89" 
End With

BaseItem returns or sets the item in the base field for a custom calculation. Valid only for data fields.

With Worksheets("Sheet1").Range("A3").PivotField 
 .Calculation = xlDifferenceFrom 
 .BaseField = "ORDER_DATE" 
 .BaseItem = "5/16/89" 
End With

Calculation returns or sets an XlPivotFieldCalculation value that represents the type of calculation performed by the specified field. This property is valid only for data fields.

With Worksheets("Sheet1").Range("A3").PivotField 
    .Calculation = xlDifferenceFrom 
    .BaseField = "ORDER_DATE" 
    .BaseItem = "5/16/89" 
End With

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

ChildField returns a PivotField object that represents the child field for the specified field (if the field is grouped and has a child field).

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
MsgBox "The name of the child field is " & _ 
 pvtTable.PivotFields("REGION2").ChildField.Name

ChildItems returns an pivotitem that represents either a single PivotTable item (a PivotItem pivotitem) or a collection of all the items (a PivotItems pivotitem) that are group children in the specified field, or children of the specified item. Read-only.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtItem In _ 
 pvtTable.PivotFields("product") 
 .PivotItems("vegetables").ChildItems 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtItem.Name 
Next pvtItem

CubeField returns the CubeField object from which the specified PivotTable field is descended.

Sub UseCubeField() 
 
 Dim objNewSheet As Worksheet 
 Set objNewSheet = Worksheets.Add 
 objNewSheet.Activate 
 intRow = 1 
 
 For Each objPF in _ 
 Worksheets(1).PivotTables(1).PivotFields 
 If objPF.CubeField.CubeFieldType = xlHierarchy Then 
 objNewSheet.Cells(intRow, 1).Value = objPF.Name 
 intRow = intRow + 1 
 End If 
 Next objPF 
 
End Sub

CurrentPage returns or sets the current page showing for the page field (valid only for page fields). Read/write PivotItem.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
strPgName = pvtTable.PivotFields("Country").CurrentPage.Name

CurrentPageList returns or sets an array of strings corresponding to the list of items included in a multiple-item page field of a PivotTable report.

Sub UseCurrentPageList() 
 
 Dim pvtTable As PivotTable 
 Dim pvtField As PivotField 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set pvtField = pvtTable.PivotFields("[Product]") 
 
 ' To avoid run-time errors set the following property to True. 
 pvtTable.CubeFields("[Product]").EnableMultiplePageItems = True 
 
 ' Set the page list to "Food". 
 pvtField.CurrentPageList = "[Product].[All Products].[Food]" 
 
End Sub

CurrentPageName returns or sets the currently displayed page of the specified PivotTable report. The name of the page appears in the page field. Note that this property works only if the currently displayed page already exists.

ActiveSheet.PivotTables("PivotTable1") _ 
 .PivotFields("[Customers]").CurrentPageName = _ 
 "[Customers].[All Customers].[USA]"

DataRange returns a Range object as shown in the following table.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
Worksheets("Sheet1").Activate 
pvtTable.PivotFields("REGION").DataRange.Select

DataType returns an XlPivotFieldDataType value that represents the type of data in the PivotTable field.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
Select Case pvtTable.PivotFields("ORDER_DATE").DataType 
 Case Is = xlText 
 MsgBox "The field contains text data" 
 Case Is = xlNumber 
 MsgBox "The field contains numeric data" 
 Case Is = xlDate 
 MsgBox "The field contains date data" 
End Select

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

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotFields("Year").DragToColumn = False

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

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotFields("Year").DragToData = False

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

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotFields("Year").DragToHide = False

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

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotFields("Year").DragToPage = False

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

Worksheets(1).PivotTables("Pivot1") _ 
 .PivotFields("Year").DragToRow = False

DrilledDown true if the flag for the specified PivotTable field or PivotTable item is set to "drilled" (expanded, or visible).

ActiveSheet.PivotTables("PivotTable3") _ 
 .PivotFields("state").DrilledDown = False

EnableItemSelection when set to False, disables the ability to use the field dropdown in the user interface. The default value is True.

Sub UseEnableItemSelection() 
 
 Dim pvtTable As PivotTable 
 Dim pvtField As PivotField 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set pvtField = pvtTable.RowFields(1) 
 
 ' Determine setting for property and enable if necessary. 
 If pvtField.EnableItemSelection = False Then 
 pvtField.EnableItemSelection = True 
 MsgBox "Item selection enabled for fields." 
 Else 
 MsgBox "Item selection is already enabled for fields." 
 End If 
 
End Sub

EnableMultiplePageItems used for specifying whether check boxes are present in the filter drop-down list for fields in the page area.

Function returns or sets the function used to summarize the PivotTable field (data fields only).

ActiveSheet.PivotTables("PivotTable1") _ 
 .PivotFields("Sum of 1994").Function = xlSum

GroupLevel returns the placement of the specified field within a group of fields (if the field is a member of a grouped set of fields).

Worksheets("Sheet1").Activate 
If ActiveCell.PivotField.GroupLevel = 1 Then 
 MsgBox "This is the highest-level parent field." 
End If

Hidden this property is used to hide the individual levels of an OLAP hierarchy.

HiddenItems returns an pivotitem that represents either a single hidden PivotTable item (a PivotItem pivotitem) or a collection of all the hidden items (a PivotItems pivotitem) in the specified field. Read-only.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtItem In pvtTable.PivotFields("Product").HiddenItems 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtItem.Name 
Next pvtItem

HiddenItemsList returns or sets a Variant specifying an array of strings that are hidden items for a PivotTable field.

Sub UseHiddenItemsList() 
 
 ActiveSheet.PivotTables(1).PivotFields(1).HiddenItemsList = _ 
 Array("[Product].[All Products].[Food]", _ 
 "[Product].[All Products].[Drink]") 
 
End Sub

IncludeNewItemsInFilter allows developers to specify whether excluded or included items should be tracked when manual filtering is applied to the PivotField.

IsCalculated true if the PivotTable field is a calculated field or item.

set pt = Worksheets(1).PivotTables("Pivot1") 
For Each fld in pt.PivotFields 
 If fld.IsCalculated Then pt.EnableFieldDialog = False 
Next

IsMemberProperty returns True when the PivotField contains member properties.

Sub CheckForMembers() 
 
 Dim pvtTable As PivotTable 
 Dim pvtField As PivotField 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set pvtField = pvtTable.PivotFields(1) 
 
 ' Determine if member properties exist and notify user. 
 If pvtField.IsMemberProperty = True Then 
 MsgBox "The PivotField contains member properties." 
 Else 
 MsgBox "The PivotField does not contain member properties." 
 End If 
 
End Sub

LabelRange returns a Range object that represents the cell (or cells) that contain the field label.

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
Set pvtField = pvtTable.PivotFields("ORDER_DATE") 
Worksheets("Sheet1").Activate 
pvtField.LabelRange.Select

LayoutBlankLine true if a blank row is inserted after the specified row field in a PivotTable report. The default value is False.

With ActiveSheet.PivotTables("PivotTable1") _ 
 .PivotFields("state") 
 .LayoutBlankLine = True 
End With

LayoutCompactRow specifies whether or not a PivotField is compacted (items of multiple PivotFields are displayed in a single column) when rows are selected.

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

LayoutPageBreak true if a page break is inserted after each field. The default value is False.

With ActiveSheet.PivotTables("PivotTable1") _ 
 .PivotFields("state") 
 .LayoutPageBreak = True 
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

MemoryUsed returns the amount of memory currently being used by the object, in bytes.

MsgBox "Microsoft Excel is currently using " & _ 
 Application.MemoryUsed & " bytes"

Name returns or sets a String value representing the name of the object.

NumberFormat returns or sets a String value that represents the format code for the object.

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

Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable 
Set pvtField = pvtTable.PivotFields("ORDER_DATE") 
Select Case pvtField.Orientation 
 Case xlHidden 
 MsgBox "Hidden field" 
 Case xlRowField 
 MsgBox "Row field" 
 Case xlColumnField 
 MsgBox "Column field" 
 Case xlPageField 
 MsgBox "Page field" 
 Case xlDataField 
 MsgBox "Data field" 
End Select

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

ParentField returns a PivotField object that represents the PivotTable field that's the group parent of the specified object. The field must be grouped and must have a parent field.

Worksheets("Sheet1").Activate 
MsgBox "The active field is a child of the field " & _ 
 ActiveCell.PivotField.ParentField.Name

ParentItems returns an pivotitem that represents either a single PivotTable item (a PivotItem pivotitem) or a collection of all the items (a PivotItems pivotitem) that are group parents in the specified field. The specified field must be a group parent of another field. Read-only.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtItem In pvtTable.PivotFields("product").ParentItems 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtItem.Name 
Next pvtItem

PivotFilters returns or sets the PivotFilters for the specified PivotField object.

Position returns or sets a value that represents the position of the field (first, second, third, and so on) among all the fields in its orientation (Rows, Columns, Pages, Data).

PropertyParentField returns a PivotField object representing the field to which the properties in this field pertain.

Sub CheckParentField() 
 
 Dim pvtTable As PivotTable 
 Dim pvtField As PivotField 
 
 Set pvtTable = ActiveSheet.PivotTables(1) 
 Set pvtField = pvtTable.PivotFields(4) 
 
 ' Check for member properties and notify user. 
 If pvtField.IsMemberProperty = False Then 
 MsgBox "No member properties present." 
 Else 
 MsgBox "The parent field of the members is: " & _ 
 pvtField.PropertyParentField 
 End If 
 
End Sub

RepeatLabels returns or sets whether item labels are repeated in the PivotTable for the specified PivotField.

ServerBased true if the data source for the specified PivotTable report is external and only the items matching the page field selection are retrieved.

For Each fld in ActiveSheet.PivotTables(1).PageFields 
 If fld.ServerBased = True Then 
 r = r + 1 
 Worksheets(2).Cells(r, 1).Value = fld.Name 
 End If 
Next

Subtotals returns or sets subtotals displayed with the specified field. Valid only for nondata fields.

Worksheets("Sheet1").Activate 
ActiveCell.PivotField.Subtotals(2) = True

TotalLevels returns the total number of fields in the current field group. If the field isn't grouped, or if the data source is OLAP-based, TotalLevels returns the value 1. Read-only Long.

Worksheets("Sheet1").Activate 
MsgBox "This group has " & _ 
 ActiveCell.PivotField.TotalLevels & " levels

Value returns or sets a String value that represents the name of the specified field in the PivotTable report.

VisibleItems returns an pivotitem that represents either a single visible PivotTable item (a PivotItem pivotitem) or a collection of all the visible items (a PivotItems pivotitem) in the specified field. Read-only.

Set nwSheet = Worksheets.Add 
nwSheet.Activate 
Set pvtTable = Worksheets("Sheet2").Range("A1").PivotTable 
rw = 0 
For Each pvtItem In pvtTable.PivotFields("Product").VisibleItems 
 rw = rw + 1 
 nwSheet.Cells(rw, 1).Value = pvtItem.Name 
Next

VisibleItemsList returns or sets a Variant specifying an array of strings that represent included items in a manual filter applied to a PivotField.

ActiveSheet.PivotTables("PivotTable2").PivotFields("[Customer].[Customer Geography] & _ 
.[Country]").VisibleItemsList = Array("[Customer].[Customer Geography].[Country].&[Australia]") 
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Customer].[Customer Geography] & _ 
.[State-Province]").VisibleItemsList = Array("") 
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Customer].[Customer Geography] & _ 
.[City]").VisibleItemsList = Array("") 
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Customer].[Customer Geography] & _ 
.[Postal Code]").VisibleItemsList = Array("") 
ActiveSheet.PivotTables("PivotTable2").PivotFields("[Customer].[Customer Geography] & _ 
.[Full Name]").VisibleItemsList = Array("") 

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

PivotFields.Parent returns the parent object for the specified object.