vba - 使用 VBA 更新 Excel 切片器选择的性能不佳

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

用户单击 X 轴上带有日期的柱形图。单击列时，将在包含日期列表的切片器中选择相应的日期。该列表将随着时间的推移继续增长。

为非 OLAP 数据源(我的情况)设置切片器选择的唯一方法(据我所知)是为每个切片器项目单独设置 selected = true 。由于每次设置都会触发重新计算，这对于具有许多项目的切片器来说非常慢。

显示问题的小代码示例:

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

之前也有人问过类似的问题:

Selecting multiple slicer items at once without a refresh

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

建议是:

Application.EnableEvents = false

或者

Application.Calculation = xlCalculationManual

更新:我还注意到，尽管关闭了事件和计算，但实际上所有数据透视表都在重新计算!

对我来说，这些选项都不起作用，也不会提高性能。计算确实被推迟并且没有触发任何事件。尽管如此， selected=true 的每次迭代大约需要 1.5 秒。整个操作大约需要 5 分钟才能完成。

我的切片器连接到多张工作表中的 23 个数据透视表 (!)。基础数据(MS Access DB 连接)大约有 60,000 行，其中包含约 20 个变量，这并不多。

任何帮助表示赞赏。

最佳答案

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

请尝试将其添加到更新切片器代码所在的正上方:

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

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

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

想要查询更多的信息:
Speed up pivot table filtering VBA code
Turn Off PT Calc
MSDN: ManulaUpdate

希望有帮助!

关于vba - 使用 VBA 更新 Excel 切片器选择的性能不佳，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/21498126/