Poor performance updating Excel slicer selection using VBA

Question

I am simulating a click on an Excel Slicer using VBA but have run into serious performance problems.

The user clicks on a column graph with dates on the X-axis. When clicking on a column, the corresponding date is selected in a slicer containing the list of dates. The list will continue to grow with time.

The only way (to my knowledge) to set slicer selection for non-OLAP data sources (my case) is to set selected = true individually for each slicer item. As a recalculation is triggered on each setting this is very slow for slicers with many items.

Small code example showing the problem:

On Error GoTo Err_Handler:

Dim SC As SlicerCache
Set SC = ActiveWorkbook.SlicerCaches("Slicer_DATE")

Dim SI As SlicerItem

Application.EnableEvents = False
Application.Calculation = xlCalculationManual

For Each SI In SC.SlicerItems
    SI.Selected = True
Next

Err_Handler:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

Similar questions have been asked before:

Selecting multiple slicer items at once without a refresh

Pivot Slicer Update To Slow, Can I pause all functions until slicer update is complete?

There the suggestion is either:

Application.EnableEvents = false

or

Application.Calculation = xlCalculationManual

UPDATE: I also notice that despite turning off events and calculation, all pivot tables are in fact recalculating!

For me, neither of these options work and do not improve the performance. Calculation is indeed postponed and no events are triggered. Still, each iteration of the selected=true takes around 1.5 seconds. In total the operation takes around 5 minutes to complete.

My slicer is connected to 23 pivot tables (!) in multiple sheets. The underlying data (MS Access DB connection) is around 60,000 rows with ~20 variables which is not that much.

Any help is appreciated.

Answer 1

PivotTables have a ManualUpdate property which can be set to True. Setting this property for all your pivots might help speed up your code.

Please try adding this right above where your update slicer code is:

Dim PT As PivotTable
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook

For Each ws In wb.Sheets
    For Each PT In ws.PivotTables
        PT.ManualUpdate = True
    Next PT
Next ws

And then add this after you've updated the slicer:

For Each ws In wb.Sheets
    For Each PT In ws.PivotTables
        PT.ManualUpdate = False
    Next PT
Next ws

For more information:
Speed up pivot table filtering VBA code
Turn Off PT Calc
MSDN: ManulaUpdate

Hope that helps!

Answer 2

What you need to do is Duplicate the Slicer and Duplicate the field as a slicer AND a report filter see http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values-in-formulas/

Then use the CurrentPage property to select the item:

Private Sub SelectPivotItem(FieldName As String, Itemname As String)
    Dim PT As PivotTable, PTF As PivotField, PTI As PivotItem
    Set PT = shtInt.PivotTables("PivotTable1")
    Set PTF = PT.PivotFields(FieldName)
    PTF.ClearAllFilters
    PTF.CurrentPage = Itemname
End Sub

Answer 3

This helps massively with performance. The same approach applies to selecting multiple PivotItems in VBA.

1. Set the pivot tables to ManualUpdate.
2. Hide all the sheets that contain pivot tables affected by the Slicer while you're doing the multiple slicer selections.

Don't forget to set the pivot tables to not ManualUpdate when done, and set the sheets visible afterwards if you need to

It's very strange because even with Application.ScreenUpdating = False this still helps enormously.

I got my PivotItems selection macro down from 3 minutes to 3 seconds by doing this. Quite a stark difference.

Unfortunately for Slicers this is still slow, around 30s. I'm looking to switch back to doing this with VBA and PivotItems, and avoiding the use of Slicers completely, unless I can find another way to improve performance.