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:
In the next example I’ve dragged the Value field up and now the data area grouping is for years within data fields:
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:
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:
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
EDIT:
In the comments below Petra identified a much faster way using PivotTable.DataPivotField. DataPivotField contains the Values field, whether or not it’s visible. So,
If DataPivotField.Orientation <> 0tests whether the Values field is present.
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”:
And so does this:
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:
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:
(1 and 2 return the two other data fields)
Furthermore, if you check the
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,
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
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.
Very handy to know, Doug. I have an awful lot of code that checks whether a DataField is called “Values”, as I didn’t know that users could change it. Now I know better, and how to avoid the issue. Not to mention that there’s one more thing that some non-English install won’t balk at.
Instead of testing each non-data field for a data field with a matching name, I’d do it the other way around: test each data field for a non-data field with a matching name. Quicker. Now…what to do with the millisecond I just saved?
I do it in that order, because I can limit to just the DataFields. Otherwise I’d have to go through just the Column and Row fields, or go through all the PivotFields and check their orientation. So I settled for readability over speed.
There’s a potential problem with your code: In the unlikely event that the source data has a field called ‘Values’ in it that happens to be in the Pivot as a Row or Column, then your function returns nothing. You can see this if you add the following line above exit_point:
If GetValueField Is Nothing Then Stop
That’s my surname with a ‘d’ on the end: Weird.
That is, Weir, Weird, that is.
Although I like your intricate thought process ;), I’m using Office 2010 and simple DataPivotField (“PT.DataPivotField.Name”) works for me..
It’s possible that it might not be so in all Office versions…
Hi Petra. Thanks! I think you’ve pointed the way to a simpler method. The big caveat is that PT.DataPivotField is always something (Not Nothing) even if the Values field isn’t actually visible/used in the pivot table, i.e., a pivot table with just one data field. For example, create a pivot table from a two-cell range and you’ll see that it still has a PivotDataField named Values.
So, to determine if the Values field is visible you need to test whether it has a Range, e.g.,
. Wrap that in On Error statements and then test if TestRange is nothing. Still much simpler than my lovely, intricate code. At least that’s my verdict after five minutes of testing. What do you think?
Hi Doug, you’re right, I wasn’t that thorough with my thinking. (I never am ;))
But then again, let me suggest a simpler method – you can determine whether the field is visible or not by testing .DataPivotField.Orientation – zero means the pivotfield is not visible. No evil On Error wrapping necessary 😉
Does that work for you or is there any issue I overlooked again? 🙂
Petra, but of course! Very nice. I’ll update the post soon to use this much simpler method. Thanks.
Hi, I was looking at the method which will go through each of the data fields used one by one, and below looks most effective.
Dim pf As PivotField
Dim pt As PivotTable
For Each pf In pt.VisibleFields
If pf.Orientation = xlDataField Then
pf.Orientation = xlHidden
End If
Next pf
Do you think it could apply to your cases too?
Hi Karmil,
Thanks. Yes, that’s a way. If you look at Petra’s comments just above yours, she figured out a very short method to do this. I edited this post to use her solution.