使用VBA的Excel切片器选择性能较差 [英] Poor performance updating Excel slicer selection using VBA

问题描述

我正在使用VBA模拟Excel切片器上的点击，但遇到严重的性能问题。

用户点击X轴上日期的列图。当单击列时，在包含日期列表的切片器中选择相应的日期。名单将随着时间的推移而持续增长。

为非OLAP数据源（我的例子）设置切片器选择的唯一方法是为每个切片器项目单独设置selected = true。由于在每个设置上触发重新计算，对于具有多个项目的切片器来说，这是非常慢的。

显示问题的小代码示例：

  On Error GoTo Err_Handler ：
 
 Dim SC As SlicerCache 
设置SC = ActiveWorkbook.SlicerCaches（Slicer_DATE）
 
 Dim SI As SlicerItem 
 
应用程序。 EnableEvents = False 
 Application.Calculation = xlCalculationManual 
 
对于每个SI在SC.SlicerItems 
 SI.Selected = True 
下一个
 
 Err_Handler ：
 Application.Calculation = xlCalculationAutomatic 
 Application.EnableEvents = True 
  

类似的问题以前被问过：

一次无需刷新就可以选择多个分片器

数据透视切片更新缓慢，可以暂停所有功能直到切片器更新完成？

有建议是：

  Application.EnableEvents = false 
  

或

  Application.Calculation = xlCalculationManual 
  

更新：我也注意到，尽管关闭事件和计算，所有的数据透视表实际上都是重新计算的！

对于我来说，这两个选项都不工作，不会提高性能。计算确实推迟了，没有事件被触发。仍然，selected = true的每次迭代大约需要1.5秒。总共操作大约需要5分钟才能完成。

我的切片器连接到多个工作表中的23个数据透视表（！）。底层数据（MS Access DB连接）约为60,000行，约20个变量不是那么大。

任何帮助都不胜感激。

解决方案

数据透视表有一个 ManualUpdate 属性，可以设置为真。为所有枢轴设置此属性可能有助于加快您的代码。

请尝试在您的更新切片器代码中添加以上权限：

  Dim PT As PivotTable 
 Dim wb As Workbook 
 Dim ws As Worksheet 
 
设置wb = ThisWorkbook 
 
对于每个ws在wb.Sheets 
对于每个PT在ws.PivotTables 
 PT.ManualUpdate = True 
下一个PT 
下一个ws 
  

然后在更新切片器后添加：

 对于每个ws In wb.Sheets 
对于每个PT在ws.PivotTables 
 PT.ManualUpdate = False 
下一个PT 
下一个ws 
  

有关详细信息：

加快数据透视表过滤VBA代码

关闭PT Calc

MSDN：ManulaUpdate

希望有帮助！

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.

解决方案

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!

这篇关于使用VBA的Excel切片器选择性能较差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！