excel - 删除无关单元格时需要停止 UDF 重新计算

Question

我注意到，每当我删除单元格时，我的 UDF 都会重新计算。这会在删除整个列时导致大量延迟，因为 UDF 会针对使用它的每个单元格进行调用。因此，如果您使用 1000 个 UDFS，则删除列或单元格将调用它 1000 次。

例如，将以下 UDF 放入模块中，然后使用 =HelloWorld() 从工作表中多次调用它

Function HelloWorld()
HelloWorld = "HelloWorld"
Debug.Print Now()
End Function

然后删除一行。如果您的体验和我一样，您会发现每次使用都会调用一次。

有人知道这种行为是否可以停止吗？我也很感兴趣为什么应该调用它。对我来说，这似乎是 Excel 依赖关系树中的一个缺陷，但很可能有一个很好的理由。

编辑:经过实验，我发现了更多触发 UDFS 的操作:

1. 通过调整大小(但不行)对 ListObject(即 Excel 表格)所跨越的列数进行任何更改。即使 UDF 本身不在相关的 ListObject 中，或者实际上根本不在任何 ListObject 中。
2. 在工作表中的任意位置添加新单元格或列(但不是行)。

请注意，手动计算模式在很多方面都不是一个选项。

首先，鉴于它是应用程序级设置，因此存在很大的风险，即有人会使用他们碰巧打开的任何一个电子表格的输出，而没有意识到自己处于手动计算模式。

其次，我实际上并不是在设计特定的电子表格，而是在写一本关于非开发人员如何利用编写良好的现成代码(例如 UDF)来完成他们无法完成的事情的书。示例包括动态连接或分割文本，或者 Charles Williams 在 https://fastexcel.wordpress.com/2011/07/22/developing-faster-lookups-part-2-how-to-build-a-faster-vba-lookup/ 中概述的精确匹配二分搜索 UDF。 (是的，我向他们发出了很多警告，通常基于 native 公式的解决方案将优于 UDF。但是正如您从我上面引用的线程中看到的那样，精心编写的函数可以表现良好)。

我不知道用户将如何使用这些。

在缺乏编程解决方案的情况下，看来我只需在书中指出，如果用户使用资源密集型 UDFS，则在添加或删除单元格或调整 ListObjects 大小时可能会遇到明显的延迟。即使这些 UDF 编写得非常高效。

Answer 1

插入或删除行、列或单元格将始终在自动模式下触发重新计算。 (您可以通过将 =NOW() 添加到空工作簿并插入或删除内容来检查这一点)

问题应该是什么(意外)情况将单元格标记为脏，以便重新计算。有一个(可能不完整的)此类内容的列表: http://www.decisionmodels.com/calcsecretsi.htm

看起来我需要添加一些关于 VBA UDF 的内容(尚未测试 XLL UDF
- 它们的行为可能有所不同，因为它们以与 VBA UDF 不同的方式注册)