Identify a Pivot Table’s Values Field

Identify Pivot Table Values Field

Over the next few posts I plan to delve into a couple of functions I’ve written to identify areas in a pivot table. I also want to do a quick post on a pivot quirk I noticed recently. I then plan to roll it up into a post on my new-and-improved per-pivot-item conditional formatting tool. It’s good to have plans, right? Anyways, let’s get started with a function to identify a pivot table’s Values field.

I deduced the following just by messing around – I couldn’t find anything on the web about identifying a Values field. If I got something wrong, or if you have a better way to do this, please leave a comment.

What is a Values Field?

The Values field is the one that appears when you have more than one data field. Its location in the Rows or Columns area of the pivot table dialogs controls the grouping of those data fields. In the following example, I’ve grouped the data area by data fields within years. In other words, the two summing data fields appear side-by-side for each year:

Values Field by years then values

In the next example I’ve dragged the Value field up and now the data area grouping is for years within data fields:

Values Field by values then years

Some pivot table layouts, such as the one below, don’t show the word “Values” anywhere in the pivot table, but it still shows in the pivot table dialog:

Values Field Column Labels

Like all pivot fields, the Values field can be renamed. Note that though I changed it to “Frodo” in the pivot table, it still says “Values” in the dialog:

Values Field by values called Frodo

Everything I’ve said about the Columns area of the pivot dialog applies to the Rows area. The Values field behaves the same way there.

Identifying the Values Field in VBA

So, anyways, I wanted a VBA function that returns a pivot table’s Values field if it has one. When figuring out how to do this I asked myself:

Is the Values field a PivotTable.DataField or a PivotTable.ColumnField/RowField?

The answer is both, kind of. So, for instance, in the examples pictured above typing the following into the immediate window returns “Values”:

? ActiveCell.PivotTable.ColumnFields("Values").Name

And so does this:

? ActiveCell.PivotTable.DataFields("Values").Name

So it looks like the Values field is both a data and column (or row) field. To further confirm this, note that this statement returns True:

? ActiveCell.PivotTable.DataFields("Values").Orientation = xlColumnField

So, even though it’s both a Data and Column (or Row) field it looks like it’s a bit more of a Column field (I’m going to stop saying “or Row” now). This is backed up by the fact that you can’t refer to it’s Data personality using an index. In other words, the following returns an error:

? ActiveCell.PivotTable.DataFields(3).Name (1 and 2 return the two other data fields)

Furthermore, if you check the DataFields.Count for the example above the count is only two.

Cutting to the Chase

In addition to the above, I’ve got one more informational tidbit: if you change the name of the Values field to “Frodo,” both its Data and Column selves refer to themselves as “Frodo.” So even though, as we’ve seen above, the dialog box continues to use the word “Values” to refer to this field, ? ActiveCell.PivotTable.DataFields("Values").Name gets you a runtime error 1004.

This means that you can’t just refer to the values field using “Values” in either its DataField or ColumnField version. If you do and a user changes its name you’re out of luck.

Fortunately, this has an upside, and it’s not just that I have something to blog about. It means that a Values field name is the only field name in the pivot table that can be repeated for a Data field and a Column field. Usually two fields can’t have the same name. For example, in the examples above if you try to rename “Year” or “Values” to “Sum of Unit Cost” you’ll get a “Field name already exists” error. But in the case of a Values field both its Data and Columm/Row references will be the same name.

This means you can identify a pivot table’s Value field by finding a row or column field that has the same name as a data field. Cool, eh?

The Function

Function GetValueField(pvt As Excel.PivotTable) As Excel.PivotField
Dim pvtField As Excel.PivotField
Dim TestField As Excel.PivotField
Dim ValueField As Excel.PivotField
 
'If there's only one data field then there won't be a Values field
If pvt.DataFields.Count = 1 Then
    GoTo exit_point
End If
 
For Each pvtField In pvt.PivotFields
    On Error Resume Next
    'test each non-data field for a data field with a matching name
   Set TestField = pvt.DataFields(pvtField.Name)
    On Error GoTo 0
    If Not TestField Is Nothing Then
        'if there's a match then you've got the Values field
        Set ValueField = pvtField
        Exit For
    End If
Next pvtField
Set GetValueField = ValueField
 
exit_point:
End Function

Boom! Let me know if you’ve got a better way, anything to add, etc. And, as always, thanks for dropping by.