performance - Excel 列表对象 VBA 性能错误？

Question

我在使用列表对象(又名 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 会以这种方式运行，是否是一个错误，或者是否是与列表对象有关的某些功能，它实际上有一些真正的用途？

谢谢，
斯图尔特

Answer 1

这与您发现的“错误”无关，这非常有趣和耐人寻味。

我只是想分享一个避免计算延迟的好方法。我用这个得到了很好的结果，现在我一直在使用它。

简单地说，Excel 在“VBA 世界”和“电子表格世界”之间来回复制数据需要很长时间。

如果您一次执行所有“读取”、处理，然后一次执行所有“写入”，您将获得惊人的性能。这是使用此处记录的变体数组完成的:

http://msdn.microsoft.com/en-us/library/ff726673.aspx#xlFasterVBA

在标记为: 的部分中在单个操作中读取和写入大块数据

我能够重构一些需要 5 分钟才能运行的代码，并将其缩短到 1.5 分钟。重构花了我 10 分钟，这太棒了，因为它是相当复杂的代码。