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

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)

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

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.
--
Gary's Student


"RobC" wrote:

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)

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

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


"Gary''s Student" wrote:

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.
--
Gary's Student


"RobC" wrote:

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)