Excel 列表对象 VBA 性能错误？
我在使用列表对象（又名 Excel 表）的 Excel 应用程序上遇到性能问题。我怀疑这可能是一个错误，但尽管我在谷歌上搜索，但我找不到任何关于它的参考。我已经为我的应用程序开发了一种解决方法，但我感兴趣的是是否有人可以深入了解为什么会发生这种情况。
注意：我在 Windows Vista 上使用 Excel 2007。设置如下：我有一个电子表格，其中将数据保存在列表对象中，其中包含可以通过命令按钮启动的 VBA 代码；此代码可能会对工作表上任意数量的单元格进行多次编辑，因此在进行任何编辑之前，Excel 的计算模式将设置为“手动”。
我遇到的问题是，如果当前活动的单元格位于列表对象内，则将计算模式设置为手动似乎没有任何效果。因此，如果用户碰巧在同一实例中打开大量计算工作簿，则 VBA 代码运行速度会非常慢。我几乎不得不拆开我的应用程序才能发现这是由活动单元引起的；我创建了一个新的工作簿，其中包含此场景的简单版本，以确认我的应用程序没有某种损坏。
我已经用这个做了很多测试用例，下面是我发现的结果：
虽然看起来一般与计算有关，但计算模式切换时仍然存在时间差异手动和自动...
- 手动 = 7.64 秒
- 自动 = 9.39 秒
手动模式仅比自动模式快不到 20%。但我的期望是它们或多或少是相同的，考虑到问题似乎是即使在手动模式下也会开始计算。
与活动单元格不在列表对象上时相比，结果有很大不同......
- 手动 = 0.14 秒
- 自动 = 3.23 秒
现在，手动运行速度快了 50 倍，自动运行显示计算时间不应超过 3.2 秒！因此，现在第一个测试看起来可能在手动模式下运行了计算两次，在自动模式下运行了近 3 次。
再次重复此测试，这次是在任何单元格中都没有计算公式的情况下，突然间它看起来并不那么糟糕，
- 活动单元格是列表对象&计算为手动 = 0.17 秒
- 活动单元格是列表对象&计算自动 = 0.20 秒
- 活动单元格为空且未激活。计算为手动 = 0.14 秒
- 活动单元格为空且未激活。计算自动 = 0.18 秒
仍然较慢，但现在仅慢了 10-20%，因此不易察觉。但这确实表明该问题必须以某种方式与计算有关，否则它应该与第一次测试一样长。
如果有人想创建这些测试来亲自查看，设置如下：
- 添加了列表对象的新工作簿（不必链接到任何数据）
- 添加一些需要 excel 一段时间才能计算的公式（我只是重复 '=1*1' 30,000 次）
- 编写一个快速的 VBA 代码，它将； （i）循环对单元格进行简单编辑数百次，（ii）并记录所花费的时间
- 然后只需运行代码，同时更改列表对象和空单元格之间的活动单元格
我很想听听是否有人可以解释为什么 Excel 会以这种方式运行，是否是一个错误，或者是否有一些与列表对象有关的功能实际上有一些真正的用途？
谢谢， 斯图尔特
I have an issue with performance on an excel application which uses List Objects (AKA Excel Tables). I suspect it may be a bug, but despite my Googling I could not find any reference of it. I've already developed a workaround for my application, but what I’m interested in is if anyone can give any insight into why this happens.
Note: I’m using Excel 2007 on Windows Vista. The setup is as follows: I have a spreadsheet which holds data in a List Object, with VBA code which can be kicked off via a command button; this code may make several edits to any number of cells on the worksheet, so Excel’s Calculation mode is set to Manual prior to any edits.
The problem I’ve encountered is that if the currently active cell is within the List Object, then setting the Calculation Mode to manual seems to have no effect whatsoever. So if a user happens to have a heavy calculation workbook open in the same instance, then the VBA code runs very slowly. I practically had to pull my application apart to discover that this was caused by the active cell; and I created a new workbook with simple version of this scenario to confirm that there wasn’t some sort of corruption on my application.
I’ve been doing a number of test cases with this, and below are the results from what I’ve found:
Although it seems generally related to the calculation, there is still a time difference when the calculation mode is switched between Manual and Automatic...
- Manual = 7.64 secs
- Automatic = 9.39 secs
Manual mode is just fewer than 20% faster than Automatic. But my expectation was they’d be more or less the same, considering the issue seems to be the calculation kicking off even when in Manual mode.
Compare that to when the active cell is not on a List Object, and the results are vastly different...
- Manual = 0.14 secs
- Automatic = 3.23 secs
Now, the Manual run is 50 times faster, and Automatic run shows that the calculation shouldn’t have taken any more than 3.2 secs! So now the first test looks like it might have run the Calculation twice while in Manual mode, and nearly 3 times while in Automatic mode.
Repeating this test again, this time in an instance with no calculation formula in any cells, and suddenly it doesn’t seem as bad,
- Active cell is List Object & Calc is Manual = 0.17 secs
- Active cell is List Object & Calc is Automatic = 0.20 secs
- Active cell is Empty & Calc is Manual = 0.14 secs
- Active cell is Empty & Calc is Automatic = 0.18 secs
It’s still slower, but now it’s only by 10-20%, making it unnoticeable. But this does show that the issue must be related to the Calculation in some way, as otherwise it should have taken just as long as the first test.
If anyone wants to create these tests to see for themselves, the setup is as follows:
- New Workbook with a List Object added (doesn’t have to be linked to any data)
- Add some formula that will take excel a while to calculate (I just did ‘=1*1’ repeated 30,000 times)
- Write a quick VBA code which will; (i) loop through a simple edit of a cell several hundred times, (ii) and record the time it took
- Then just run the code while changing the active cell between the List Object and an empty cell
I’d be very interested to hear if anyone can explain why Excel behaves in this way, and if is a bug or if is some feature to do with List Objects which actually has some genuine use?
Thanks,
Stuart
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
这与你发现的“bug”无关，这是相当有趣和耐人寻味的。
我只是想分享有一个很好的方法可以避免计算延迟。我用它取得了很好的效果，现在我一直在使用它。
简单地说，Excel需要很长时间在“VBA世界”和“电子表格世界”之间来回复制数据。
如果您一次执行所有“读取”、处理，然后立即执行所有“写入”，您将获得惊人的性能。这是使用此处记录的变体数组完成的：
http://msdn.microsoft .com/en-us/library/ff726673.aspx#xlFasterVBA
标记为：在单个数据块中读取和写入大数据块的部分操作
我能够重构一些需要 5 分钟才能运行的代码，并将其缩短到 1.5 分钟。重构花了我 10 分钟，这很令人惊奇，因为它是相当复杂的代码。
This is not relative to the "bug" you found, which is quite interesting and intriguing.
I just want to share that there is a great way to avoid calculation delays. I had fantastic results with this and now I use it all the time.
Simply put, Excel takes a long time copying data back and forth between the "VBA world" and the "spreadsheet world".
If you do all the "reads" at once, process, and then do all the "writes" at once, you get amazing performance. This is done using variant arrays as documented here:
http://msdn.microsoft.com/en-us/library/ff726673.aspx#xlFasterVBA
in the section labeled: Read and Write Large Blocks of Data in a Single Operation
I was able to refactor some code I had that took 5 minutes to run and bring it down to 1.5 minutes. The refactoring took me 10 minutes, which is amazing because it was quite complex code.
关于表性能（以及一般性能）：
我知道这是一个老问题，但我想记录下来。
旧版 Excel 和 2007 年后版本之间发生的一件事是，Excel 现在会激活任何 PasteSpecial 操作的目标工作表。您无法通过关闭 ScreenUpdating 并手动进行计算来覆盖它。这种激活将使纸张可见，并导致无法控制的闪烁。
我原来的 VBA 代码在运行 Excel 2000 的老式单处理器 XP 机器上运行得非常快。在现代机器上对 Excel 2013 的更改令人震惊，代码执行速度极其缓慢。影响性能的三个方面是从一张工作表到另一张工作表的特殊粘贴、需要激活工作表的任何其他代码（缩放级别、高级过滤器、工作表级别范围名称等）以及自动化工作表保护/取消保护。
这太糟糕了，因为 PasteSpecial 帮助“清理”您复制的数据（直接使用 .Copy 到目标会引发偶尔的错误）。
因此，您需要检查您的代码，并确保您使用直接分配给所需数据类型的正确属性（例如，从 Value、Value2、Text 和 Formula 中），而不是 PasteSpecial。
例如 .Range("MYRANGE").Value = .Cells(5, 7).Value2
您还需要谨慎地抵制在整个代码中使用 Select 和 Activate。
如上所述，您会在 Excel 论坛中找到关于最后一点的许多评论，这些评论将声明您“从不”需要使用激活，这显然是不正确的，因为 Excel 中的一些内容仅适用于或需要活动工作表。了解通过特定方法或对象的使用自动强制激活的情况也将有助于编码。不幸的是，您不会看到太多这方面的文档。
更新：
关于条件格式，您会在各种论坛中发现许多关于 Excel 在遇到大量条件格式单元格时速度缓慢的抱怨。我怀疑这会影响 Excel 表格，因为它们有很多表格格式选项。为了测试这一点，我拿了我们使用的一个大工作簿，该工作簿当前被格式化为多个工作表，其上具有相同样式的 Excel 表格。
将表格转换为常规范围后，我注意到代码执行速度没有差异。这似乎表明使用 Excel 表格格式远远优于对您自己的单元格数组进行条件格式设置。
Regarding Table performance (and performance, in general):
I know this is an old question, but I want to get this documented.
One thing that changed between older versions of Excel and the post-2007 versions is that Excel now activates the target sheet of any PasteSpecial operation. You cannot override it by turning off ScreenUpdating and making calculations manual. Such Activation WILL make the sheet visible, and cause uncontrollable flicker.
My original VBA code ran very fast on an old, single-processor XP box running Excel 2000. The change to Excel 2013 on a modern machine was stunning in the terrible slowness of code execution. The three areas that kill performance are PasteSpecial from one sheet to another, any other code that requires activating sheets (Zoom level, Advanced Filter, Sheet-Level range names, etc), and automating sheet protection/unprotection.
This is too bad, because PasteSpecial helped "cleanse" data you copy (Direct use of .Copy to a target will throw the occasional error).
So you need to review your code and make sure you are using direct assignment to the right property for the data type you need (from among Value, Value2, Text, and Formula, for example), instead of PasteSpecial.
e.g. .Range("MYRANGE").Value = .Cells(5, 7).Value2
You also need to be scrupulous in resisting use of Select and Activate throughout your code.
As referenced above, many comments you'll find in Excel fora about that last point will make statements that you "never" need to use Activation, which is clearly untrue, since several things in Excel only apply to or require active sheets. Understanding the cases where activation is forced automatically by a particular method or use of an object will help in coding as well. Unfortunately, you won't see much in the way of documentation of this.
Update:
Regarding Conditional Formatting, you'll find many complaints in various fora about the slowness of Excel when encountering a large number of Conditionally-formatted cells. I suspected this would impact Excel Tables since they have many table format options. To test this, I took a large workbook we use that is currently formatted as several worksheets with the same style of Excel Table on them.
After converting the tables to a conventional range, I noticed no difference in speed of code execution. This would seem to indicate that use of Excel Table formats is far superior to conditionally-formatting your own arrays of cells.