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.

Canale

dicks blog meets stackoverflow :) Interesting question as I do confirm this holds, even though your UDF doesn't have an associated cell range, volatile function combined with it or Application.Volatile. A simple clear contents doesnt trigger the UDF, a delete row does. I will await a post from Charles Williams with interest. – Haigh

Have you tried setting excel calculation method to Manual. Manual calculation mode means that Excel will only recalculate all open workbooks when you request it by pressing F9 or Ctrl-Alt-F9, or when you Save a workbook. For workbooks taking more than a fraction of a second to recalculate it is usually better to set Calculation to Manual. – Keyte

Hi Izzymo. I steer clear of Manual Calc Mode whenever I can, on account of the following: * Possibility that output being used without model being refreshed. I'm generally not the only user. * If the first workbook you open during the day happens to be one that was saved in Manual Calculation mode then all other workbooks that you open during the day will also be switched to Manual Calculation mode. Possibly without you even knowing. Often manual calc mode is used to mask the symptoms of a poorly designed spreadsheet. Not in all cases, and I'm not saying it's all bad. Just mostly avoidable. – Canale

Answer 1

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.

For example, users can write this kind of UDF:

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

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).

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.

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).

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.

Villalobos

re your comment **The reason for this is that the argument passed to the UDF is in fact a Range **: my simple function doesn't pass any arguments at all. So I don't see why Excel would need to recalculate it. – Canale

@jeffreyweir: Yes you're right. What I meant is that a UDF may depend on a Range argument. It seems that the developers chose to (unfortunately) update all UDFs instead of updating only those with Range arguments (i.e. those that may require an update following a column deletion). – Villalobos

Answer 2

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)

Tabshey