删除不相关的单元格时需要停止UDF重新计算 [英] Need to stop UDFs recalculating when unrelated cells deleted

问题描述

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

I've noticed that my UDFs recalculate whenever I delete cells. This causes massive delays when deleting entire columns, because the UDF gets called for each and every cell it is used in. So if you're using 1000 UDFS, then deleting a column or cell will call it 1000 times.

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

By way of example, put the following UDF in a module, then call it from the worksheet a bunch of times with =HelloWorld()

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

然后删除一行.如果您的经历像我的，那么您会发现每次使用实例都会调用一次.

Then delete a row. If your experience is like mine, you'll see it gets called once for every instance of use.

任何人都知道是否可以停止这种行为?我也很感兴趣为什么要调用它.在我看来，Excel的依赖树中似乎有缺陷，但很可能有充分的理由.

Anyone have any ideas whether this behavior can be stopped? I'd also be interested why it should get called. Seems like a flaw in Excel's dependency tree to me, but there may well be a good reason.

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

After experimentation, I've found more actions that trigger UDFS:

1. 对ListObject(即Excel表)通过调整大小(但不行)所跨越的列的数量进行任何更改.即使UDF本身不在相关的ListObject中，或者实际上根本不在任何 ListObject中.

1. Any change to the number of columns that a ListObject (i.e. Excel Table) spans through resizing (but not rows). Even if the UDFs themselves aren't in the ListObject concerned, or in fact in any ListObject at all.

1. 在工作表中的任何位置(而不是行)添加新的单元格或列.

请注意，在多个方面都不能选择手动计算模式".

Note that Manual Calc Mode isn't an option on several fronts.

首先，考虑到这是应用程序级的设置，它仅仅带来了太大的风险，即某人将使用他们碰巧已打开的任何电子表格的输出而没有意识到他们处于手动计算模式.

Firsty, given that it is an application-level setting, it simply presents too great a risk that someone will use the output of any one of the spreadsheets they happen to have open without realizing they are in manual calculation mode.

其次，我实际上不是在设计特定的电子表格，而是写一本关于非开发人员如何利用编写好的现成代码(例如UDF)来执行本应超出其范围的事情的书.示例包括动态串联或拆分文本，或Charles Williams在

Secondly, I'm not actually designing a particular spreadsheet but rather am writing a book about how non-developers can utilize well-written off-the-shelf code such as UDFs to do things that would otherwise be beyond them. Examples include dynamically concatenating or splitting text, or the exact match binary search UDF that Charles Williams outlines at https://fastexcel.wordpress.com/2011/07/22/developing-faster-lookups-part-2-how-to-build-a-faster-vba-lookup/ (And yes, I give them much warning that usually a native formula-based solution will outperform a UDF. But as you'll see from the thread I've referenced above, carefully written functions can perform well).

我不知道用户将如何使用它们.

I don't know how users will employ these.

在没有编程解决方案的情况下，我只需要在书中指出，如果用户使用了占用大量资源的UDFS，则在添加或删除单元格或调整ListObject的大小时，用户可能会遇到明显的延迟.即使这些UDF被有效地编写.

In the absence of a programming solution, it looks like I'll just have to point out in the book that users may experience significant delay when adding or deleting cells or resizing ListObjects if they have resource-intensive UDFS employed. Even if those UDFs are efficiently written.

推荐答案

不幸的是，我不认为有可能防止删除无关"单元格时重新计算UDF.这样做的原因是，传递给UDF的参数实际上是一个Range对象(而不仅仅是单元格中的值).删除无关"单元实际上可以修改Range.

Unfortunately, I don't believe it's possible to prevent a UDF from being recalculated when "unrelated" cells are deleted. The reason for this is that the argument passed to the UDF is in fact a Range object (not just the value within the cell(s)). Deleting "unrelated" cells can actually modify the Range.

例如，用户可以编写这种UDF:

For example, users can write this kind of UDF:

Function func1(rng)
    func1 = rng.Address & " (" & Format(Now, "hh:mm:ss") & ")"
End Function

诚然，这不是编写UDF的常用方法(推荐).通常，它应取决于内容(值)而不是容器(范围).

Admittedly, this is not the common (and recommended) approach to write a UDF. It should normally depend on the content (value) and not the container (range).

在这里，我只是返回参数的地址.当重新计算UDF时，我还会附加一个时间戳以发出信号.如果删除工作表上的任何列，则会重新计算具有此UDF的所有单元格.但是，如果您插入一列，则不会(使新列右边)的单元格保持不变，并且值(单元格地址)错误.结果与插入/删除行相同.奇怪的是，插入单个单元格会强制重新计算所有UDF.

Here I'm just returning the address of the argument. I also append a timestamp to signal when the UDF is recalculated. If you delete any column on the worksheet, all cells with this UDF are recalculated. But not if you insert a column, leaving the cells on the right (of this new column) unchanged and with the wrong value (cell address). The results are the same with inserting/deleting rows. Strangely, inserting a single cell does force recalculation of all UDF's.

我试图删除Range上的依赖项".但是，即使UDF的参数键入as double，其行为也相同(而不是像我的示例那样将其保留为Variant).

I tried to remove the "dependency" on the Range. But the behavior is also the same even if the UDF's argument is typed as double (instead of leaving it as a Variant like in my example).

正如您所解释的，删除列将强制重新计算UDF.这是有道理的，因为UDF 可以依赖于Range参数.对于UDF来说，这是一个聪明的设计，是另一回事.

As you explained, deleting a column will force UDFs to be recalculated. This makes sense because a UDF can depend on the Range argument. Wether this is a smart design for a UDF is a different matter.

这篇关于删除不相关的单元格时需要停止UDF重新计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！