Need to stop UDFs recalculating when unrelated cells deleted

Question

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.

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.

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.

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

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.
   2. Adding new cells or columns anywhere in the sheet (but not rows).

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.

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.

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.

Answer 1

Inserting or deleting a row or column or cell will always trigger a recalc in automatic mode. (You can check this by adding =NOW() to an empty workbook and inserting or deleting things)

The question should be what (unexpected) circumstances flag a cell as dirty so that it gets recalced. There is a (probably incomplete) list of such things at http://www.decisionmodels.com/calcsecretsi.htm

Looks like I need to add some words about VBA UDFs (have not tested XLL UDFs
- they may behave differently since they are registered in a different way to VBA UDFs)