vba 如何使“清晰切片器”代码更高效？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18919106/
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
How to make 'Clear Slicers' Code More Efficient?
提问by ARich
I'm working with six slicers on a summary sheet in Excel 2010. Each slicer is connected to 26 pivot tables.
我正在 Excel 2010 中的汇总表上使用六个切片器。每个切片器都连接到 26 个数据透视表。
I used the code below in the past to clear slicer selections and it worked with minimal lag. This time the code takes 6-7 seconds to execute.
我过去使用下面的代码来清除切片器选择，并且它的延迟最小。这次代码执行需要 6-7 秒。
Dim oSlicerC As SlicerCache
For Each oSlicerC In ActiveWorkbook.SlicerCaches
oSlicerC.ClearManualFilter
Next oSlicerC
I can't find anything online to speed up the macro/make it more efficient.
我在网上找不到任何东西来加速宏/使其更有效率。
I tried setting ScreenUpdating, DisplayAlerts, and EnableEventsto Falseand I tried setting Calculationto xlCalculationManualat the beginning of my code.
我尝试设置ScreenUpdating,DisplayAlerts和EnableEventstoFalse并尝试在代码的开头设置Calculationto xlCalculationManual。
I thought about iterating through each SlicerItemto de-select each one, but with up to 100 items in a few slicers I'm not sure that would be any faster.
我想过遍历每一个SlicerItem来取消选择每一个，但是在几个切片器中最多有 100 个项目，我不确定这会更快。
I'm not looking for code as I'd like to take a stab at it myself, but I'm not sure which path I should take or if there's even a more efficient alternative to what I'm already using.
我不是在寻找代码，因为我想自己尝试一下，但我不确定我应该走哪条路，或者是否有更有效的替代方案来替代我已经在使用的方法。
回答by Nutley
I had a similar problem, settled on:
我有一个类似的问题，解决了：
Dim oSlicerC As SlicerCache
For Each oSlicerC In ActiveWorkbook.SlicerCaches
If oSlicerC.FilterCleared = False Then oSlicerC.ClearManualFilter
Next oSlicerC`
回答by Bella O.
It's an old post but there is also another possibility.
这是一个旧帖子，但还有另一种可能性。
If you have the data in an excel table you can call on that table
如果您有 Excel 表格中的数据，您可以调用该表格
tbl.AutoFilter.ShowAllData
This will also reset all slicers very fast.
这也将非常快速地重置所有切片器。
with 173'561 rows and other things in between my program.
在我的程序之间有 173'561 行和其他东西。
for each example above: 6.88 sec my example: 6.92 sec
对于上面的每个示例：6.88 秒我的示例：6.92 秒
Perhaps there are situation where one or the other is faster. e.g. it has to reset more than just a few slicers my version should be faster.
也许存在其中一个更快的情况。例如，它必须重置不仅仅是几个切片器，我的版本应该更快。
回答by nir
i see it was posted long ago, but still this could be usefull: i just defined 2 parameters for sliceritems, and while looping on first items, ran another loop to unselect all. '''
我看到它是很久以前发布的，但这仍然很有用：我刚刚为 sliceritems 定义了 2 个参数，并且在循环第一项时，运行了另一个循环以取消选择所有。'''
For Each oSi In oScR.SlicerItems
If oSi.HasData Then
For Each oSiT In oScR.SlicerItems:
If oSiT.HasData Then oSiT.Selected = False
Next
oSi.Selected = True
End If
Next
'''
'''
