What caused that PivotTableUpdate? Episode IV

Pivot Wars 3 Small

So if you’ve been merrily following along, then you’ll know that we’ve got a function that picks up which PivotField just got filtered in a PivotTable, with a couple of exceptions:

  • If users change the PivotFilter list, but leave the same count of things visible, or
  • If any stinky PageField filters read ‘Multiple Items’ both before and after the change.

We’re about to add something more to our previous function to handle those cases.

With a few very minor exceptions, the majority of our previous function remains unchanged, and looks like so:

Function PivotChange_GetFilterName(pt As PivotTable) As String

    Dim strLastUndoStackItem As String
    Dim pf As PivotField
    Dim pi As PivotItem
    Dim i As Long
    Dim lngVisibleItems As Long
    Dim lngFields As Long
    Dim strVisibleItems As String
    Dim bIdentified As Boolean
    Dim strElimination As String
    Dim bElimination As Boolean
    Dim dicFields As Object 'This holds a list of all visible pivotfields
    Dim dicVisible As Object 'This contains a list of all visible PivotItems for a pf
    Dim varKey As Variant
   
    Application.EnableEvents = False
   
    On Error Resume Next 'in case the undo stack has been wiped or doesn't exist
    strLastUndoStackItem = Application.CommandBars("Standard").FindControl(ID: = 128).Control.List(1)
    On Error GoTo 0
   
    If strLastUndoStackItem <> "" Then
        For i = 1 To pt.VisibleFields.Count
            Set pf = pt.VisibleFields(i)
            With pf
                If .Orientation <> xlDataField And .Name <> "Values" Then
                    If .Orientation <> xlPageField Then
                        strVisibleItems = strVisibleItems & .Name & "|" & .VisibleItems.Count & "||"
                    Else
                        'pf.VisibleItems.Count doesn't work on PageFields
                        'So for PageFields we’ll record what that PageField’s filter currently displays.
                        strVisibleItems = strVisibleItems & .Name & "|" & .LabelRange.Offset(, 1).Value & "|" & .EnableMultiplePageItems & "||"
                    End If
                End If
            End With
        Next i
        Select Case strLastUndoStackItem
            Case "Filter", "Select Page Field Item", "Slicer Operation"
                With pt
                    If InStr(.Summary, "|") > 0 Then 'the Summary field contains previously recorded info about pivot layout etc
                        If .Summary <> strVisibleItems Then
                            For i = 0 To UBound(Split(.Summary, "||"))
                                If Split(.Summary, "||")(i) <> Split(strVisibleItems, "||")(i) Then
                                    PivotChange_GetFilterName = "PivotFilter changed: " & Split(Split(.Summary, "||")(i), "|")(0)
                                    bIdentified = True
                                    Exit For
                                End If
                            Next i
                        End If 'If .Summary <> strVisibleItems Then
                       
                        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 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.
                            lngFields = 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
                                        Else
                                            'It *might* be this field
                                            lngFields = lngFields + 1
                                            strElimination = strElimination & .Name & ";"
                                        End If 'If .AllItemsVisible = True The
                                    End If 'If .Orientation <> xlDataField And .Name <> "Values" Then
                                End With
                            Next pf
                           
                            If lngFields = 1 Then
                                PivotChange_GetFilterName = "PivotFilter changed: " & Left(strElimination, Len(strElimination) - 1)
                                bIdentified = True
                            Else

But now, to track down those exceptions, we’re going to have to work some magic. Or as I put it in the code:

    '                           =================================
    '                            We will have to use *The Force*
    '                           =================================

For each visible PivotField, we’re going to compare the list of what is visible now to what was visible before. Bear in mind that we don’t know in advance what PivotTable the user is going to tamper with. Given this, you may be asking yourself:

C3PO 2

You’re dead right, Golden Rod. And that will require us to either keep a duplicate of every PivotTable in the workbook, or to extract every single pivotitem in the entire workbook to a Dictionary or Array that we would have to continually update.

Unless we use the Force. Yes, let’s use the Force. After all, it is all around us.

With the help of The Force:

  1. We’re only going to record the settings for the PivotTable that just changed
  2. Then we’re going to programatically hit the Undo button
  3. Then we’re going to play spot-the-difference between what things looked like after the user made that change:
    After2

    …and what it looked like before they made that change:
    Before2

  4. Then we’re going to restore things back to the way the user wanted them:
    After2

 
To do this, we’re going to employ a Dictionary.
Of Dictionaries.
Like so:

                                If Not bIdentified Then
                                ' The If statement above is purely there to catch the possibility that
                                ' we failed to find the filter in the above code because it's the first
                                ' time we've run the code, meaning nothing was previously stored in pt.summary
                               
                                    'Create master dictionary
                                    Set dicFields = CreateObject("Scripting.Dictionary")
                                   
                                    'Cycle through all visible pivotfields, excluding totals
                                    For i = 0 To lngFields - 1
                                        'Create dicVisible: a dictionary for each PivotField that contain visible PivotItems
                                        Set dicVisible = CreateObject("Scripting.Dictionary")
                                        Set pf = pt.PivotFields(Split(strElimination, ";")(i))
                                        With pf
                                        If .Orientation <> xlPageField Then
                                            For Each pi In .VisibleItems
                                                With pi
                                                    dicVisible.Add .Name, .Name
                                                End With
                                            Next pi
                                        Else:
                                            'Unfortunately the .visibleitems collection isn't available for PageFields
                                            ' e.g. SomePageField.VisibleItems.Count always returns 1
                                            ' So we'll have  to iterate through the pagefield and test the .visible status
                                            ' so we can then record just the visible items (which is quite slow)
                                             For Each pi In .PivotItems
                                                With pi
                                                    If .Visible Then
                                                        dicVisible.Add .Name, .Name
                                                    End If
                                                End With
                                            Next pi
                                        End If 'If .Orientation = xlPageField Then
                                        'Write dicVisible to the dicFields master dictionary
                                        dicFields.Add .Name, dicVisible
                                        End With
                                    Next i

Great, so we know what’s visible now. But what about before?
Well, in keeping with the Star Wars theme, anyone fancy a prequel?

                                    Application.Undo

And now we’ll check how the old compares to the new:

                                    For Each varKey In dicFields.keys
                                        Set pf = pt.PivotFields(varKey)
                                        Set dicVisible = dicFields.Item(varKey)
                                       
                                        'Test whether any of the items that were previously hidden are now visible
                                            If pf.Orientation <> xlPageField Then
                                                For Each pi In pf.VisibleItems
                                                    With pi
                                                        If Not dicVisible.exists(.Name) Then
                                                            bIdentified = True
                                                            PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
                                                            Exit For
                                                        End If
                                                    End With
                                                Next
                                            Else 'pf.Orientation = xlPageField
                                                lngVisibleItems = dicVisible.Count
                                                i = 0
                                                For Each pi In pf.PivotItems
                                                    With pi
                                                        If .Visible Then
                                                            If Not dicVisible.exists(.Name) Then
                                                                bIdentified = True
                                                                PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
                                                                Exit For
                                                            Else: i = i + 1 'this is explained below.
                                                            End If 'If Not dicVisible.exists(.Name) Then
                                                        End If 'If .Visible Then
                                                    End With
                                                Next
                                               
                                                ' For non-PageFields, we know that the number of .VisibleItems hasn't changed.
                                                ' But we *don't* know that about Pagefields, and an increase in the amount of
                                                ' .VisibleItems won't be picked up by our Dictionary approach.
                                                ' So we'll check if the overall number of visible items changed
                                                If Not bIdentified And i > lngVisibleItems Then
                                                    bIdentified = True
                                                    PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
                                                    Exit For
                                                End If
                                            End If 'If pf.Orientation <> xlPageField Then
                                            If bIdentified = True Then Exit For
                                        Next

Great, we’ve found it. Now it’s time for operation Application.Redo!

WAIT! There is no Application.Redo method!

junk

 
Aw, screw it…let’s use that Force stuff again:

                                    'Resore the original settings
                                    Application.CommandBars("Standard").FindControl(ID:=129).Execute

…and then set the Hyperdrive for home:

                                End If 'If Not bIdentified Then
                            End If 'If lngFields = 1 Then
                        End If 'If Not bIdentified Then
                    End If 'If InStr(.Summary, "|") = 0 Then
                End With
            Case Else: PivotChange_GetFilterName = strLastUndoStackItem
        End Select
    End If 'If strLastUndoStackItem <> ""
    pt.Summary = strVisibleItems
    Application.EnableEvents = True
   
    'Cleanup
    Set dicFields = Nothing
    Set dicVisible = Nothing

End Function

 
 
Stormtrooper: Let me see your sample file.

Obi-Wan: [with a small wave of his hand] You don’t need to see his sample file.

Stormtrooper: We don’t need to see his sample file.

Obi-Wan: These aren’t the sample files you’re looking for.

Stormtrooper: These aren’t the sample files we’re looking for.

Obi-Wan: He can go about his pivoting.

Stormtrooper: You can go about your pivoting.

Obi-Wan: Move along.

Stormtrooper: Move along… move along.

 
 
 
Okay, okay…here’s the sample file.

Can you stop remotely squeezing my throat now, Darth?
PivotChange_20140802

 
Test Pattern

Update

I have re-factored the code in the sample file as per the post at Broken Arrow.

13 Comments

  1. Geeko says:

    Great post. If you use Excel in a different language make sure to change the following code:
    strLastUndoStackItem = Application.CommandBars(“Standard”).Controls(“&Undo”).Control.List(1)

    in german:
    strLastUndoStackItem = Application.CommandBars(“Standard”).Controls(“&Rückgängig”).Control.List(1)

    Normally VBA codes are working with english names/variables but in this case you use the caption of the element. And that is written in the language you are using.

  2. Jeff Weir says:

    Thanks Geeko. Turns out we can use this to get the last item in the Undo stack:

    strLastUndoStackItem = Application.CommandBars("Standard").FindControl(ID:=128).List(1)

    …and this to exectute the Redo method:

    Application.CommandBars("Standard").FindControl(ID:=129).Execute

    I’m not sure whether that string of “Standard” will pose any problems in international versions or not. I believe it works it German so perhaps it works in all.
    I also note that while you can replace that “Standard” with 3 for the Redo line e.g.

     Application.CommandBars(3).FindControl(ID:=129).Execute

    …doing the same for the strLastUndoStackItem line generates a “Object variable or With block variable not set” error.

  3. Jeff Weir says:

    Ah. The index for the “Standard” commandbar in my version of Excel is 14, not 3. So if I replace “Standard” with 14 in the above snippets it works perfectly. Which begs a couple of questions:
    1. Is there any need to? i.e. are there versions of Excel that use something different than “Standard” to reference this bar?
    2. Is that index number of 14 ever likely to vary for any reason? Or will it always refer to the “Standard” commandbar?

    Anyone know?

    Edit: Walkenbach knows. Excel 2007 Power Programming with VBA:
    If you are writing code that will be used by a different language version of Excel, avoid using the Caption property to access a particular shortcut menu. The Caption property is language-specific, so your code will fail if the user has a different language version of Excel.

    The CommandBar names are not internationalized, so a reference to CommandBars(“Standard”) will always work.

  4. snb says:

    In Dutch:

    Application.CommandBars(“Standaard”)

  5. Jeff Weir says:

    Thanks for the heads-up, snb.

  6. Rich says:

    This whole article… just rept(“very “,100) clever

  7. Jeff Weir says:

    =REPT(“Thanks, Rich”,1)

  8. TonyW says:

    Wow! Dare I suggest Jeff, purely for the benefit of the Excel community, that maybe your better half should trot off on hols more often! (By the way are you eating properly?)

  9. Jeff Weir says:

    Hi Tony. To be sure, I’ve got much more time than usual to write up posts. And my diet ain’t what it used to be…

  10. snb says:

    @Jeff

    You might consider simplifying your code:

    E.g. instead of

    lngVisibleItems = dicVisible.Count
      i = 0
      For Each Pi In pf.PivotItems
        With Pi
          If .Visible Then
            If Not dicVisible.exists(.Name) Then
              i = i + 1 'this is explained below.
              bIdentified = True
              PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
              Exit For
            End If 'If Not dicVisible.exists(.Name) Then
          End If 'If .Visible Then
        End With
      Next
                                                   
      ' For non-PageFields, we know that the number of .VisibleItems hasn't changed.
      ' But we *don't* know that about Pagefields, and an increase in the amount of
      ' .VisibleItems won't be picked up by our Dictionary approach.
      ' So we'll check if the overall number of visible items changed
      If Not bIdentified And i <> lngVisibleItems Then
        bIdentified = True
        PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
        Exit For
      End If 'If i > lngVisibleItems Then
     End If 'If pf.Orientation <> xlPageField Then
     If bIdentified = True Then Exit For

    You might stick to:

    For j = 1 To pf.PivotItems.Count
        If Not dicVisible.exists(pf.PivotItems(j).Name) Then Exit For
      Next
      If j <= pf.PivotItems.count Then Exit For
  11. Jeff Weir says:

    @snb: I don’t believe your revision will work. You need to test the list of visible items now against the list of visible items from before. Whereas your code tests each item – whether visible or not – against the list of visible items from before. Meaning that unless all items are visible, your revision will always think that this field changed, even if it didn’t.

    Unfortunately, for PageFields you can’t iterate through the .VisibleItems collection, as there isn’t one. If your field is not a pagefield, then yes you can take a simpler approach, as I have in the block immediately above the one you have posted above:

    'Test whether any of the items that were previously hidden are now visible
        If pf.Orientation <> xlPageField Then
            For Each pi In pf.VisibleItems
                With pi
                    If Not dicVisible.exists(.Name) Then
                        bIdentified = True
                        PivotChange_GetFilterName = "PivotFilter changed: " & pf.Name
                        Exit For
                    End If
                End With
            Next
        Else 'pf.Orientation = xlPageField

    So you can see from the above that I only take the more complicated route that you commented on if the simpler route isn’t available.

    Granted, I could do without that i = i + 1 stuff in the excerpt. Except that I think it will be faster to iterate through the PivotItems collection natively than to use the index number to iterate through it. Given PivotFields regularly have upwards of tens of thousands of items in them, I’m choosing execution speed over code simplicity.

  12. […] this particular code, I’m trying to find out what PivotField (if any) got filtered. I have three separate tests – […]

  13. Jeff Weir says:

    Have refreshed the sample file in this post with re-factored code as per http://dailydoseofexcel.com/archives/2014/07/23/broken-arrow/

Posting code or formulas in your comment? Use <code> tags!

  • <code lang="vb">Block of code goes here</code>
  • <code lang="vb" inline="true">Inline code goes here</code>
  • <code>Formula goes here</code>

Leave a Reply

Here's how to update your reports of company and nearly any web data: