excel - Need to stop UDFs recalculating when unrelated cells deleted -

i've noticed udfs recalculate whenever delete cells. causes massive delays when deleting entire columns, because udf gets called each , every cell used in. if you're using 1000 udfs, deleting column or cell call 1000 times.

by way of example, put following udf in module, call worksheet bunch of times =helloworld()

function helloworld() helloworld = "helloworld" debug.print now() end function 

then delete row. if experience mine, you'll see gets called once every instance of use.

anyone have ideas whether behavior can stopped? i'd interested why should called. seems flaw in excel's dependency tree me, there may reason.

edit: after experimentation, i've found more actions trigger udfs:

1. any change number of columns listobject (i.e. excel table) spans through resizing (but not rows). if udfs aren't in listobject concerned, or in fact in any listobject @ all.
   2. adding new cells or columns anywhere in sheet (but not rows).

note manual calc mode isn't option on several fronts.

firsty, given application-level setting, presents great risk use output of 1 of spreadsheets happen have open without realizing in manual calculation mode.

secondly, i'm not designing particular spreadsheet rather writing book how non-developers can utilize well-written off-the-shelf code such udfs things otherwise beyond them. examples include dynamically concatenating or splitting text, or exact match binary search udf charles williams outlines @ https://fastexcel.wordpress.com/2011/07/22/developing-faster-lookups-part-2-how-to-build-a-faster-vba-lookup/ (and yes, give them warning native formula-based solution outperform udf. you'll see thread i've referenced above, written functions can perform well).

i don't know how users employ these.

in absence of programming solution, looks i'll have point out in book users may experience significant delay when adding or deleting cells or resizing listobjects if have resource-intensive udfs employed. if udfs efficiently written.

unfortunately, don't believe it's possible prevent udf being recalculated when "unrelated" cells deleted. reason argument passed udf in fact range object (not value within cell(s)). deleting "unrelated" cells can modify range.

for example, users can write kind of udf:

function func1(rng)     func1 = rng.address & " (" & format(now, "hh:mm:ss") & ")" end function 

admittedly, not common (and recommended) approach write udf. should depend on content (value) , not container (range).

here i'm returning address of argument. append timestamp signal when udf recalculated. if delete column on worksheet, cells udf recalculated. not if insert column, leaving cells on right (of new column) unchanged , wrong value (cell address). results same inserting/deleting rows. strangely, inserting single cell force recalculation of udf's.

i tried remove "dependency" on range. behavior same if udf's argument typed as double (instead of leaving variant in example).

as explained, deleting column force udfs recalculated. makes sense because udf can depend on range argument. wether smart design udf different matter.