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!