加快数据透视表过滤 VBA 代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/764504/
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
Speed up pivot table filtering VBA code
提问by Ankit
I have a pivot table with a pivot field and contain many items. I've VBA code logic to decide if the pivot value should be visible or not. The problem is excel recalculates pivot table for each field shown or hidden which makes it very slow. I would like something where it recalculates only once, after all the values are set. I tried using Application.Calculation = xlCalculationManual but it didnt help.
我有一个带有数据透视字段的数据透视表并包含许多项目。我有 VBA 代码逻辑来决定枢轴值是否应该可见。问题是 excel 为每个显示或隐藏的字段重新计算数据透视表，这使得它非常慢。在设置所有值后，我想要只重新计算一次的东西。我尝试使用 Application.Calculation = xlCalculationManual 但它没有帮助。
vba code that I am using is something like this
我正在使用的 vba 代码是这样的
For i = 1 To oPivotField.PivotItems.Count
If (oPivotField.PivotItems(i).Name = "TestCondition") Then
oPivotField.PivotItems(i).Visible = True 'Recalulates pivot table
Else
oPivotField.PivotItems(i).Visible = False 'Recalulates pivot table
End If
Next
I am to do this manually by uncheck the "show all" box and re-check for the fields I want visible. This cause Excel to recalculate once and show only the pivot items I want visible. I would like to do same thing via VBA code.
我将通过取消选中“全部显示”框并重新检查我想要可见的字段来手动执行此操作。这会导致 Excel 重新计算一次并仅显示我希望可见的数据透视项。我想通过 VBA 代码做同样的事情。
I even tried using
我什至尝试使用
Application.ScreenUpdating = False
Application.DisplayAlerts = False
but didn't work.
但没有用。
回答by Michelle
Oh! I just solved that one:
哦！我刚刚解决了那个：
At the beginning of your code, turn off the auto-update like this:
在代码的开头，像这样关闭自动更新：
PivotTable.ManualUpdate = True
And then at the end of the code, turn it back on:
然后在代码的末尾，将其重新打开：
ActiveSheet.PivotTables("PivotTable1").PivotCache.Refresh
I found this thread searching for help writing code that determines if a PivotTable value should be visible. What is behind your oPivotField? That's the part I'm missing!
我发现这个线程正在寻找帮助编写确定数据透视表值是否应该可见的代码。你的 oPivotField 背后是什么？这就是我缺少的部分！
回答by barrowc
PivotTableobjects have a ManualUpdateproperty which might be what you are looking for.
PivotTable对象具有ManualUpdate可能是您正在寻找的属性。
See http://www.ozgrid.com/VBA/hide-pivot-fields.htmfor some related code
有关一些相关代码，请参阅http://www.ozgrid.com/VBA/hide-pivot-fields.htm
回答by Michael
pivottable.ManualUpdate [ = setting ]
True causes RefreshTable to clear data from the pivot table, rather than refreshing it
False allows RefreshTable to work normally.
Default is False.
This property is reset to False automatically after the calling procedure ends (important)
pivottable.ManualUpdate [ = setting ]
True 导致 RefreshTable 从数据透视表中清除数据，而不是刷新它
False 允许 RefreshTable 正常工作。
默认值为假。
调用过程结束后，此属性会自动重置为 False（重要）
This property should be set to true just before you make an update (e.g. changing pivot item Visible property)
So your code would look like:
在您进行更新（例如更改数据透视项 Visible 属性）之前，应将此属性设置为 true，
因此您的代码将如下所示：
For i = 1 To oPivotField.PivotItems.Count
If (oPivotField.PivotItems(i).Name = "TestCondition") Then
oPivotField.Parent.ManualUpdate = True
oPivotField.PivotItems(i).Visible = True 'doesn't recalculate pivot table because ManualUpdate is set to True
Else
oPivotField.Parent.ManualUpdate = True
oPivotField.PivotItems(i).Visible = False 'doesn't recalculate pivot table because ManualUpdate is set to True
End If
Next
'setting pivot table ManualUpdate property to False might be redundant at this point because it gets reset to false immediately after you set Visible property of oPivotField
oPivotField.Parent.ManualUpdate = False
oPivotField.Parent.Update()
As a conclusion, ManualUpdate property change doesn't stay for long (in my tests, I could see that it gets reset to false as soon as possible, so that's why I recommended you to set it to true whenever you want to make a change for a pivot item)
总之，ManualUpdate 属性更改不会持续很长时间（在我的测试中，我可以看到它会尽快重置为 false，这就是为什么我建议您在想要进行更改时将其设置为 true对于枢轴项）
For more info on what means an update in Excel, you can check the following:
Pivot Refresh vs. Update – is there a real difference?
有关 Excel 中更新意味着什么的更多信息，您可以查看以下内容：
数据透视刷新与更新 – 有真正的区别吗？
References:
Title: Programming Excel with VBA and .NET
By: Jeff Webb, Steve Saunders
Print ISBN: 978-0-596-00766-9| ISBN 10: 0-596-00766-3
Ebook ISBN: 978-0-596-15951-1| ISBN 10: 0-596-15951-X
参考文献：
标题：使用 VBA 和 .NET 对 Excel 进行编程
作者：Jeff Webb、Steve Saunders
打印 ISBN：978-0-596-00766-9| ISBN 10：0-596-00766-3
电子书 ISBN：978-0-596-15951-1| ISBN 10：0-596-15951-X
回答by sabrekillah
Try to add the following. Helped to give extra speed in my case aswell.
尝试添加以下内容。在我的情况下也有助于提高速度。
At the beginning:
一开始：
Application.Calculation = xlCalculationManual
At the end:
在末尾：
Application.Calculation = xlCalculationAutomatic
回答by MAF
Save a copy of your workbook and save it as "excel 93-2007" file. Then try your code. Hope this helps you.
保存工作簿的副本并将其另存为“excel 93-2007”文件。然后试试你的代码。希望这对你有帮助。
