使用 VBA 更新 Excel 切片器选择的性能不佳
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21498126/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Poor performance updating Excel slicer selection using VBA
提问by Jonas
I am simulating a click on an Excel Slicer using VBA but have run into serious performance problems.
我正在使用 VBA 模拟对 Excel 切片器的单击，但遇到了严重的性能问题。
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.
用户单击 X 轴上带有日期的柱形图。单击列时，将在包含日期列表的切片器中选择相应的日期。该列表将随着时间的推移继续增长。
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.
为非 OLAP 数据源（我的情况）设置切片器选择的唯一方法（据我所知）是为每个切片器项目单独设置 selected = true 。由于每次设置都会触发重新计算，这对于具有许多项目的切片器来说非常慢。
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?
Pivot Slicer Update To Slow, 我可以暂停所有功能直到切片器更新完成吗？
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.
对我来说，这些选项都不起作用，也不会提高性能。计算确实被推迟并且没有触发任何事件。尽管如此， selected=true 的每次迭代大约需要 1.5 秒。整个操作大约需要 5 分钟才能完成。
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.
我的切片器连接到多张工作表中的 23 个数据透视表 (!)。基础数据（MS Access DB 连接）大约有 60,000 行，其中包含约 20 个变量，这并不多。
Any help is appreciated.
任何帮助表示赞赏。
回答by ARich
PivotTables have a ManualUpdateproperty which can be set to True. Setting this property for all your pivots might help speed up your code.
数据透视表有一个ManualUpdate属性，可以设置为True. 为所有枢轴设置此属性可能有助于加快代码速度。
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
有关更多信息：
加速数据透视表过滤 VBA 代码
关闭 PT Calc
MSDN：ManulaUpdate
Hope that helps!
希望有帮助！
回答by John Hayes
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/
您需要做的是复制切片器并将字段复制为切片器和报告过滤器，请参阅http://www.powerpivotpro.com/2010/12/another-way-to-get-and-use-slicer-values -在公式中/
Then use the CurrentPage property to select the item:
然后使用 CurrentPage 属性选择项目：
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
回答by baldmosher
This helps massively with performance. The same approach applies to selecting multiple PivotItems in VBA.
这对性能有很大帮助。相同的方法适用于在 VBA 中选择多个 PivotItem。
- Set the pivot tables to ManualUpdate.
- Hide all the sheets that contain pivot tables affected by the Slicer while you're doing the multiple slicer selections.
- 将数据透视表设置为 ManualUpdate。
- 在进行多个切片器选择时，隐藏包含受切片器影响的数据透视表的所有工作表。
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.
这很奇怪，因为即使使用 Application.ScreenUpdating = False 这仍然有很大帮助。
I got my PivotItems selection macro down from 3 minutes to 3 seconds by doing this. Quite a stark difference.
通过这样做，我将 PivotItems 选择宏从 3 分钟缩短到 3 秒。很明显的区别。
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.
不幸的是，对于切片器来说，这仍然很慢，大约 30 秒。我希望切换回使用 VBA 和 PivotItems 执行此操作，并完全避免使用切片器，除非我能找到另一种提高性能的方法。
