How do I get a UDF to return existing value rather than recalc

[Guest]

I need to get a UDF to sometimes return its existing value rather than
recalc. I am happy for the function to be called but need some way of 1)
bypassing the full calc - (clearly trivial) and then 2) returning the value
that was already there before the calculation attempt (much harder!)

This is really for speed increases. I have a set of Excel functions that
query an Access based app and users typically have many huindreds of calls in
a single wrokbook. The underlying data in the mdb only changes occasionally
(but unpredictably)

 

[Guest]

Declare a public global variable (outside the UDF). Have the UDF store its
return value in this variable whenever it exists.

Pass a boolean flag to the UDF as an argument. The coding in the UDF would
examine the flag. If true, do the calculation; if false, just pickup the
value in the global variable and return that value.

 

[Guest]

Thanks for this. As it stands this won't work as users make 00's of calls to
the same function in any given workbook. We considered a variation of this -
effectively "caching" all function call returns in an array. This is very
iffy and gives rise to major synchronisation problems (i.e trapping worksheet
manipulation etc)


What I really need is some way for a function to know its own value at the
instant before the calculation process fires