What caused that PivotTableUpdate? Part Tres.

Okay, so:

  • In Part One, we considered the problem of determining what actually triggered a PivotTableUpdate (or it’s equally gormless twin, the PivotTableChangeSync event)
  • In Part Two we worked out that we could tell via the contents of the Undo Stack whether the update was caused by filtering, a refresh, a structure change, or other less common tweaks – and we determined that we still couldn’t tell what particular PivotFilter a user might have changed.

But you already know that.

Today, we’ll wrap this knowledge in a function that will determine what triggered a PivotTableUpdate event by reading and returning the related Undo Stack entry. If the update event was triggered by some hoodlum filtering the pivot, the function will *try* to determine which PivotField it was so that it can additionally return the name of that PivotField. I say *try* because there are no guarantees in life apart from death, taxes, and the fact that when my wife goes to sunny Spain for 6 weeks I get to stay behind in the Southern Hemisphere winter and pay for it. (Ah well…she took the kids, so we *both* get a holiday of sorts.)

So how are we going to do this? First, we’ll use that underwhelming PivotTableUpdate event as a trigger for our PivotChange function. In this case we’ll feed that PivotChange result to a messagebox, for instant feedback.

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Dim strWhatHappenned As String
strWhatHappenned = PivotChange(Target)
If strWhatHappenned <> "" Then MsgBox strWhatHappenned
End Sub

Then in our actual function PivotChange, we’ll start by retrieving the most recent item from that Undo Stack.

Dim strLastUndoStackItem As String
Dim pf As PivotField
Dim i As Long
Dim strVisibleItems As String
Dim bIdentified As Boolean
Dim strElimination As String
Dim bElimination As Boolean

Application.EnableEvents = False

On Error Resume Next 'in case the undo stack has been wiped or doesn't exist
strLastUndoStackItem = Application.CommandBars("Standard").Controls("&Undo").Control.List(1)
On Error GoTo 0

The next thing we’re going to record how many visible items there are in each visible PivotField to a pipe-delimited string, using the pf.VisibleItems property:

If strLastUndoStackItem <> "" Then
For i = 1 To pt.VisibleFields.Count
Set pf = pt.VisibleFields(i)
With pf
If .Orientation <> xlDataField Then
If .Name <> "Values" Then
If pf.Orientation <> xlPageField Then
strVisibleItems = strVisibleItems & .Name & "|" & pf.VisibleItems.Count & "||"

…unless it’s a PageField, because stinky PageFields don’t have said pf.VisibleItems property. You can pf.VisibleItems them all you want, and all you’ll ever get back is “1”. God, I hate PageFields. So for PageFields we’ll record what that PageField’s filter currently displays. This will either say “(All)” or “(Multiple Items)”, or will contain the name of just one PivotItem in the event that it’s filtered to show just one PivotItem. UPDATE: We’ll also record whether “Select Multiple Items is checked, in case a user changes that setting but changes nothing else.

strVisibleItems = strVisibleItems & .Name & "|" & pf.LabelRange.Offset(, 1).Value & "|" & .EnableMultiplePageItems & "||"
End If
End If 'If .Name <> "Values" Then
End If 'If .Orientation <> xlDataField Then
End With
Next i

Note that we didn’t use the pf.CurrentPage to retrieve the name of that stinky PageField, because if the PageField has been set so that the user can select multiple items (i.e. EnableMultiplePageItems = True) then surprise surprise pf.CurrentPage always returns “(All)” even if just one item is selected. So instead we’ll use pf.LabelRange.Offset(, 1).Value to get the filter’s title. God, I hate PageFields.

Now we’ll check if that PivotTableUpdate was caused by a user filtering a PivotField.

Select Case strLastUndoStackItem
Case "Filter", "Select Page Field Item"

If it was, we’ll use that record of currently visible items we just put together. How? Well, we’re going to compare it against the number of items that were visible before the Pivot was refreshed. If there’s a difference for a particular field, then bing! we have our culprit. But where do we get those previous pf.VisibleItems stats from? Why, we stored them somewhere clever the last time this code ran…in the PivotTable itself!

PT Summary box 2

Ever wondered what pt.Summary did? Wonder no more…it allows you to read or write whatever you want to that “Description” box above. Here’s the code that does the reading and comparison:

With pt
If InStr(.Summary, "|") = 0 Then 'the Summary field contains NO previous info about pivot layout etc
PivotChange = "PivotFilter changed: Unable to determine which one." 'Ahh, but we will, next time.
If .Summary <> strVisibleItems Then
For i = 0 To UBound(Split(.Summary, "||"))
If Split(.Summary, "||")(i) <> Split(strVisibleItems, "||")(i) Then
PivotChange = "PivotFilter changed: " & Split(Split(.Summary, "||")(i), "|")(0)
bIdentified = True
Exit For
End If
Next i
End If 'If .Summary <> strVisibleItems Then

So far, we’ve got code that will pick up most instances of a PivotField being filtered. But not all, because our method so far relies on there being a change in the number of visible items in a PivotField. Meaning if some malicious filterer (filteree?) changes the filter selection whilst leaving the same number of things visible, then we’ll miss it.

What’s more, if the PivotField is a PageField and the filter text reads ‘Multiple Items’ both before and after the change, then because we were forced to rely on picking up changes in the actual filter’s titleg rather than the unhelpful .visibleitems property, we wouldn’t have noticed any change even if the user did leave a different amount of items visible before and after. Stinky PageFields. Ought to be ashamed of yourselves.

But even in both of those cases, we’ve got one last-ditch effort to identify the culprit with the information that we already have to hand. Can you think of it? No? Come on now…how often have I said to you:

When you have eliminated the impossible, whatever remains, however improbable, must be the truth.

That’s right…once. So pay attention, Sherlock. If we check all the visible fields to see if *just one of them alone* has neither .AllItemsVisible = True nor .EnableMultiplePageItems = false, then by process of elimination, this field must be the one that triggered the change, as changes to any of the others would have been identified in the code earlier.

Here’s Watson store:

If Not bIdentified Then
'Check all the visible fields to see if *just one of them alone* has
' neither .AllItemsVisible = True nor .EnableMultiplePageItems = false.
' If that's the case, then by process of eliminaiton, this field
' must be the one that triggered the change, as changes to any of the
' others would have been identified in the code earlier.
i = 0
For Each pf In pt.VisibleFields
With pf
If .Orientation <> xlDataField And .Name <> "Values" Then
If .AllItemsVisible = True Then
'it's not this field
bElimination = True
ElseIf .Orientation = xlPageField And .EnableMultiplePageItems = False Then
' it's not this field either
bElimination = True
'It *might* be this field
i = i + 1
strElimination = strElimination & .Name & ";"
End If 'If .AllItemsVisible = True The
End If 'If .Orientation <> xlDataField And .Name <> "Values" Then
End With
Next pf

If i = 1 Then
PivotChange = "PivotFilter changed: " & Left(strElimination, Len(strElimination) - 1) & "."
ElseIf bElimination = True Then PivotChange = "PivotFilter changed: one of " & Left(strElimination, Len(strElimination) - 1) & "."
Else: PivotChange = "PivotFilter changed: Unable to determine which one."
End If 'If i = 1 Then
End If 'If Not bIdentified Then
End If 'If InStr(.Summary, "|") = 0 Then
End With

And if the PivotTableUpdate wasn’t caused by filtering, then all we need do is tell the calling procedure what that action the user performed was:

Case Else: PivotChange = strLastUndoStackItem
End Select

All that’s left now is to overwrite the old record of visible fields in that PivotTable Alt Text box with the current record, so that next time the code runs we’ll have something to reminisce about:

pt.Summary = strVisibleItems
End If 'If strLastUndoStackItem <> ""
Application.EnableEvents = True
End Function

So there we have it: a function that lets you know what triggered a PivotTableUpdate event and that does it’s *utmost* to let you know which PivotFilter was changed, if any.

Here’s a sample file with it all good to go:

Sure, it will still not capture cases where malicious filterers (filterees?) leave the same number of things visible, or where any stinky PageField filter read ‘Multiple Items’ both before and after the change. But it will warn you if that happens, and it’s not like that’s gonna happen every time.

And besides, in our upcoming season finale I’ll introduce an additional function that will do a far deeper, resource-intensive dive in that particular case.

Till then, Hasta Pronto.

What caused that PivotTableUpdate? Part Two.

In Part One, we considered the problem of determining what actually triggered a PivotTableUpdate (or it’s equally gormless twin, the PivotTableChangeSync event), with a view to identifying when a specific PivotField is filtered. Today we’re going to take a look at how we can find out more about what triggered those events. And what better place to glean information about the last action the user performed than here:


That’s right, the Undies stack. (That’s what we call it down-under). Or rather, the Undo stack to you uptight northerners. (Ok, enough of the innuendo and Double entendre.)

Go on, then…show us what’s in your undies…er…undo stack, Jeff:

Undo Pivot Actions 5

Wow: all of the above relate to some kind of action on a PivotTable. If only we could access that list, we’d have a pretty rich source from which to answer the title of this post. And we can indeed do just that.

If you want everything in that list, then you can use this code from MVP Siddharth Rout at msdn:

For i = 1 To xlApp.CommandBars("Standard").Controls("&Undo").Control.ListCount
UnDoList(i) = xlApp.CommandBars("Standard").Controls("&Undo").Control.List(i)

…and if you just want the last undo item, you can use this:

Let’s take a look at how all the different things that raise a PivotTableUpdate event get reflected in that Undo list. If an action isn’t listed in the below table, then as far as I know from my rather in-exhaustive testing, it doesn’t raise a PivotTableUpdate event.

Actions and associated undo stack text v2

There’s a few things worth noting about the above.

  • Firstly, we can now clearly determine whether the update was caused by filtering, a refresh, a structure change (in which case the Undo Stack just says ‘Pivot’, or other less common tweaks.
  • Secondly, while we can use this to confirm whether or not a PivotTableUpdate event was in fact caused by someone adjusting a PivotFilter, we still can’t tell which filter.
  • Thirdly, it’s surprising just how many things trigger an update – which is why it will be good to call out filter changes explicitly in the event that we want to sync lots of large pivots.
  • And finally, one of the actions – adding/amending/deleting a Calculated Field – actually clears the Undo Stack. How weird is that?

Okay, that’s enough for today…I’ve got to go cook my dinner. (My wife is in Spain with the kids, and so apparently it won’t cook itself). Tune in next time, when we’ll look at how we can write a routine that leverages off the undo stack, and that also helps us determine not only that a PivotField was filtered, but which PivotField it was.

What caused that PivotTableUpdate? Part One.

Over at MSDN, ToWIZ writes:

I’m looking for a detailed guide that would help users understand the details of PivotTable events and their functioning. The documentation has only one sentence for each event. Surely Microsoft doesn’t consider this to be a detailed guide?

Apparently so.


  • The PivotTableUpdate event occurs after a PivotTable report is updated on a worksheet.
  • The PivotTableChangeSync occurs after changes to a PivotTable. This event can be used only in Excel 2010 projects.

And let me get this straight: reading between the lines on this extensive documentation, you’re telling me that the new event does the same thing as the old event, except for the fact that it only works in Excel 2010 or later? Wow. Quite some improvement.

I can’t find a single thing that differentiates these two events – either on the web or in practice. Here, you try. Put this in a Sheet Module where you’ve got some pivots, and play around with them:

Private Sub Worksheet_PivotTableChangeSync(ByVal Target As PivotTable)
Debug.Print "PivotTableChangeSync: " & now()
End Sub

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
Debug.Print “PivotTableUpdate: ” & now()
End Sub

Did you find a single case where one got triggered and the other didn’t? Me neither.


Eric van Rooijen did: Ticking the ‘Defer Layout Update’ option in the PivotFilter Pane means that only the Worksheet_PivotTableChangeSync gets triggered when changing PivotTable Fields.

You’ve almost got to wonder if someone on the team at Redmond misheard the brief for that 2010 addition. I’m sure they meant to introduce a new handler called PivotFieldChangeSync that would allow us to do something that we currently can’t – work out exactly what a user was doing to a PivotField (e.g. filtering a particular field). Instead we got this rehash with less scope than the old thing, as near as I can tell.

Which is a pity. Because PivotTables are the best thing about Excel, and PivotFilters (and now Slicers) are how users interact with that best thing. And here we are in Excel 2013 with still no good way to find out what PivotFilter a user just changed.

Why would you want to know that? Plenty of reasons…one of which is so that then you could efficiently sync a whole bunch of pivots in a dashboard in the case that slicers aren’t an option (e.g. the pivots are on different caches, or users have an earlier version of Excel that doesn’t support slicers). In that scenario, relying on just one of the above event handlers to run the code is damned inefficient, because:

  • It would get triggered by any old change that you make to a Pivot.
  • Even when it does get triggered by someone actually changing a PivotFilter, the question remains…which one did they change? Those events won’t tell you…

Don’t go thinking that a Worksheet_Selection change is going to help you work it out, because clicking on a PivotFilter or Slicer does not change your selection. And don’t go thinking you can use a Worksheet_Change event to capture the filter refreshing neither…when a PivotTable gets updated, the whole PivotTable gets updated, meaning that Worksheet_Change event returns the entire range that a PivotTable occupies – not just the cell behind the filter that a user just changed.

All this means that if you wanna say sync a whole bunch of pivots – and slicers aren’t an option – then relying solely on one of those insensitive Update events alone means you’ll have no choice but to sync pretty much every visible field that appears in the master pivot…something that may result in unacceptable delays to users if there are lots of pivots with lots of fields and with lots and lots of pivotitems in them. It could take many many minutes to iterate through all of them.

Sure, you can build some sort of userform control or array of shapes (one for each pivotfield) that the user clicks before they say filter a particular field. But that sounds complicated and quite likely bespoke to the dashboard concerned.

Or perhaps you could programatically put some invisible shapes in front of each PivotFilter, so that when when the user goes to click the PivotFilter they actually click those shapes instead, meaning you can then use application.caller to work out which shape – and therefore which PivotFilter – they clicked on. But you’d have to use SendKeys to open the filter that they just thought they clicked on, and you’d have to ensure those invisible boxes always moved with the PivotTable. Pretty invasive and unreliable stuff, really. And pretty complicated to set up.

Screw that…how ’bout a generalised solution that works right out of the box on any pivot, with no setup whatsoever?

How would you capture changes to specific PivotFields and the like?

Got a good method? Let us know in the comments. Got no idea? Tune in next time to see my approach.

Playing Peekaboo with the Used Range

I was trying to reset the used range in a spreadsheet template I inherited the other day, that had a much bigger used range than it should have had. Damned if I could get it to reset. Tried deleting rows and columns below and to the right of the actual used range. Tried this:

Sub ResetUsedRange()
Dim sht As Worksheet
Dim lng As Long

For Each sht In ActiveWorkbook.Worksheets
lng = sht.UsedRange.Rows.Count

End Sub

Still no joy.

Then after much playing around, I discovered the problem. Several problems, really:

  • If you hide a row – or adjust it’s width, then the UsedRange is extended down to that row. But only for rows. And deleting all the rows below your ‘actual’ used range soon fixes this problem.
  • Hiding columns or adjusting their width does nothing to the used range. Unless you hide a column, and then unhide it by manually dragging on the boundary of the hidden column in order to resize it. Then you just screwed up the UsedRange bigtime: the used range now extends to that column, and it is devilishly hard to reset it again. Deleting all columns to the right of your ‘actual’ used range does NOT fix the problem.

Try it. You’ll find that the extended used range this gives is much more persistent than the Spanish Football Team.

After much experimentation, I found that you can only reset the used range back to what it should be if you:

  1. Hide that pesky column again.
  2. Unhide that pesky column again, using the Right Click > Unhide method.
  3. Delete that pesky column.
  4. Delete the perfectly innocent column that took it’s place.

Then and only then could I get the used range to go back to it’s cage. Weird. But might explain why you’ve had used ranges in the past that you simply could not get to behave.

(UPDATE:As demonstrated by snb’s code, the weird result you get by resizing a hidden column disappears if you close and then reopen the workbook.)

In the course of all this, I noticed that if you hide a row, then if you put the cursor over the hidden row and double-click when this icon comes up:
Autofit Row
…then the row is unhidden.

But if you hide a column, and then double click when this comes up:
Autofit Column
…absolutely nothing happens. What’s with that?

In both cases, the Macro Recorder spits out Rows(“25:25”).EntireRow.AutoFit or Columns(“M:M”).EntireColumn.AutoFit as the case may be. But in the case of hidden columns, it just doesn’t seem to do anything.

Weird again.

NOT learning from my Errors

I was writing some formulas today that need to return TRUE if a search term appears in a cell, and FALSE otherwise. For instance, I wanted to know if FOO appeared in FOOBAR or not.

Originally I was using FIND, along these lines:

But then had some issues with case that screwed things up:

Given that for my purposes FOO was as good as Foo, I decided to replace the FIND function in this with the SEARCH function, on account of SEARCH being an insensitive bastard. (Can I say that here? No? Oops…sorry!).

So I manually edited the formula, and replaced FIND with SEACH. Then cut and pasted that formula in lots of other places. Then did lots of analysis with the output. Without learning from the error of my ways:

Whoops…that’s not how you spell SEARCH! Why didn’t you tell me Excel? Oh…because of this:

Which don’t mean there ain’t no FOO (or Foo) to be found. Rather it means “Yes, I am in fact returning an error – thank you for asking – because I have no idea what this damn SEACH does.”

Murphy’s law: I never noticed that I’d screwed it up until right at the very end of my days work.

Still, not noticing till the very end is better than not noticing.

Or as Excel would put it:
=NOT(ISERROR(SEARCH(“not noticing”,”not noticing till the end is better than not noticing.”)))

Learning from my Errors

There’s an annoying bug in VBA whereby if you’re trying to change the .visible status of a PivotItem, and if the PivotField had a number format set to General, and if you live in New Zealand, then you’re out of luck:

Unable to set visible property

If you live in the US however, you’ll be fine. No error for you.

Don’t believe me? Either change your Windows region to New Zealand and run this code, or swing down to my place and see it for your own eyes. Here’s where you’ll find me:

247 Rintoul Street

(Aside: Check out those awesome ocean views. Why if it wasn’t for that annoying continent-sized lump of Uranium and Gold Ore off to the West, we’d pretty much have 365 degree views of the entire Pacific. Fortunately they’re busy bulldozing that annoying outcrop and shipping it off to uranium reactors and jewelery stores across the globe. So we should have a completely unfettered view in 2 billion years or so).

Ok, so this issue isn’t just an issue for New Zealanders…it actually affects any place where you haven’t got your Windows ‘region’ set to US, with New Zealand being the only place where I’ve actually encountered such egocentric behavior to date. (I don’t get out much. Or rather, they don’t let me out much. Or rather they make it clear that I can go out, but I can’t come back in.)

According to IronyAaron in the comments at this thread:

When VBA recognizes the dates in the pivot cache, it reads the US version after parsing although the item is read as a locally formatted string. This therefore causes VBA to fail when recognizing Date variables.

Bummer! So write some code that filters PivotItems, and you might find that non-US users have issues, unless they change their regional settings in Windows to US beforehand. Good luck with that.

This nasty bug caused quite a bit of workaround in my FilterPivot routine. I used to do this horrible check on every single item in a potentially exhaustively long list of PivotItems in order to avoid the possibility of an error caused by this unlikely combination occurring:

If Not IsNumeric(Pi.Value) Then
'We need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
If IsDate(Pi.Value) Then
If Not bDateWarning Then
On Error GoTo ErrHandler
Err.Raise Number:=997, Description:="Can't filter dates"
On Error Resume Next
End If
Else: Pi.Visible = True
End If
Else: Pi.Visible = True
End If

But prompted by Jerry Sullivan’s comment I found that this was only an issue for non-US regional settings, and that this issue is now fixed in Excel 2013. (Thank you, Microsoft. But why the heck didn’t you tell me you’d fixed it?)

So now I can just do this:
pi.Visible = True

Or rather, I could just do that if everyone had Excel 2013. But they don’t. So I can’t. I still have to somehow catch this error. And as written above, my code rather inefficiently looks for possible trouble caused by a combination of things that is probably unlikely to occur. (I mean, how many people would dare to have their Windows region set to a non-US region while trying to filter a PivotItem that happens to be a date in a PivotField that happens to have a General format?) All that preemptive error checking can’t be good for business.

The error of my ways?

I’m sure you’ve already seen what looks to be like the error of my ways… why bother checking for errors just so I can avoid them? Why not embrase them: just plow ahead, and if the s#!t hits the fan, just deal with it. Something like this:

On Error Goto Errhandler
pi.Visible = True

'some other code

If Err.Number <> 0 Then
Select Case Err.Number
Case 1004 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
i = i + 1
ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
strDateItems(i) = pi.Value
End If
Case Else 'Some other error code here

Great: now that bug fix code in the Errhandler only gets fired up in the rare event that it is actually triggered. Sure beats neurotically checking each and every PivotItem to see if it might cause an issue.

Unfortunately in this particular case the code snippet in question sits within a larger loop, and the code immediately before needs to have an On Error Resume Next statement applied. That’s because in order to work out whether a PivotItem should be hidden, I’m adding it to a Dictionary object that also contains my desired filter terms, in order to see if it matches any of those filter terms. Which looks something like this:

On Error Resume Next
For Each pi In pfOriginal.PivotItems
dic.Add pi.Value, 1 'The 1 does nothing
If Err.Number <> 0 Then
pi.visible = true

So I’d need to put an On Error Goto Errhandler before the pi.Visible = True bit so that my bug fix code in Errhandler would get triggerred, and an On Error Resume Next bit after it, so that the Dictionary test occurs for the very next item. And those will get executed for every single PivotItem – which kind of defeats the efficiency ‘dividend’ of putting my handling code within Errhandler. So I figure I might as well just do this:

pi.Visible = True
If Err.Number = 1004 Then 'Error likely due to bug outlined at http://dailydoseofexcel.com/archives/2013/11/09/a-date-with-pivotitems/
If Not IsNumeric(pi.Value) And IsDate(pi.Value) And pfOriginal.NumberFormat = "General" Then 'Yep, definately that 'Bug
'Note that we need the Not IsNumeric bit above because VBA thinks that some decimals encased in strings e.g."1.1" are dates
i = i + 1
ReDim Preserve strDateItems(1 To i) 'Record the offending things to an array, so we can warn the user about the specific items
strDateItems(i) = pi.Value
End If
End If

Two steps forward, one step backwards.

Maybe I shouldn’t have that On Error Resume Next in there in the first place…maybe I should catch errors from the Dictionary.add in Errhandler too, or even do the dictionary check in another procedure – something that Dick mentions here. Anyone got any advice here?

All I know is that this is a lot of work-around for a combination that is pretty unlikely, but potentially fatal to someone’s analysis.

Pop quiz

Question One

You want to calculate a running (i.e. cumulative) total of the Data column.
Which of these formulas should you put in B2 and drag down, and why?

Question Two

You want to calculate a running (i.e. cumulative) total of the Data column, and subtract 1 from it.
Which of these formulas should you put in B2 and drag down, and why?


If you’re ambivalent as to the approach you would take, download and try out a slightly revised thought experiment in the attached file. Then you won’t be ambivalent.


This has a more realistic data set, where instead of subtracting 1 from the cumulative total, you want to subtract a varying list of cumulative expenses, so you can work out the cumulative net profit:
Pop Quiz v5

Take it that inserting/deleting rows is not an issue (assume the structure is locked down).

Note that this is intended to be a thought experiment/illustration about a common approach which happens to be very resource intensive, and a better solution that works just fine provided you don’t do anything else within that formula but calculate a cumulative total.

But feel free to post alternatives.