UDF recount must be stopped when deleted cells are not deleted

Question

I noticed that my UDFs are recounted whenever I delete cells. This leads to huge delays when deleting entire columns, since UDF is called for each and every cell in which it is used. Therefore, if you use 1000 UDFS, then deleting a column or cell will call it 1000 times.

As an example, put the following UDF in the module, and then name it from the sheet several times with = HelloWorld ()

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

Then delete the line. If your experience is similar to mine, you will see that it is called once for each use case.

Does anyone have any idea if this behavior can be stopped? I would also be wondering why it needs to be called. It seems like I'm wrong in the Excel dependency tree, but there might be a good reason.

Edit: after experimenting, I found more actions that run UDFS:

• Any change in the number of columns that a ListObject (i.e. an Excel spreadsheet) covers resize (but not rows). Even if the UDFs themselves are not in the corresponding ListObject or in any ListObject at all.
  2. Add new cells or columns anywhere in the sheet (but not in rows).

Please note that manual Calc mode is not an option on multiple fronts.

First of all, given that this is an application-level parameter, it simply poses too much risk that someone will use the output of any of the spreadsheets that they can open without realizing that they are in manual calculation mode.

Secondly, I don’t actually create a specific spreadsheet, but rather I am writing a book about how non-developers can use well-written off-the-shelf code, such as UDF, to do things that would otherwise be outside. Examples include dynamic concatenation or text splitting, or an exact match for the UDF binary search, which Charles Williams describes 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 a lot of warning that usually their own formula-based solution will be superior to UDF. But, as you will see from the stream I referenced above, carefully written functions may work well )

I do not know how users will use them.

In the absence of a software solution, it seems that I just have to point out in the book that users can experience a significant delay when adding or removing cells or resizing ListObjects if they use resource-intensive UDFS. Even if these UDFs are effectively written.

Answer 1

Unfortunately, I do not consider it possible to prevent the recalculation of UDF when "unconnected" cells are deleted. The reason for this is because the argument passed to UDF is actually a Range object (and not just the value inside the cell (s)). Removing "unrelated" cells can really change Range .

For example, users can write this type of UDF:

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

Admittedly, this is not a general (and recommended) approach for writing UDF. This usually depends on the content (value), and not on the container (range).

Here I just return the address of the argument. I also add a timestamp to signal when the UDF is being recounted. If you delete any column in the worksheet, all cells with this UDF will be recounted. But no, 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. Oddly enough, inserting a single cell leads to a recalculation of all UDFs.

I tried to remove the "dependency" on Range . But the behavior is also the same, even if the UDF argument is printed as double (instead of leaving it as an option, as in my example).

As you explained, deleting a column will cause UDF to recount. This makes sense because UDF may depend on the Range argument. Wether is a smart design for UDF - that's another matter.

Answer 2

Inserting or deleting a row or column or cell will always cause a repeat in automatic mode. (You can verify this by adding = NOW () to an empty book and inserting or deleting things)

The question should be that (unforeseen) circumstances put the cell as dirty so that it will be restored. There is a (possibly incomplete) list of such things at http://www.decisionmodels.com/calcsecretsi.htm

It looks like I need to add a few words about VBA UDF (they have not tested XLL UDFs - they can behave differently, as they are registered differently with VBA UDF)