Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Recalc Time | Excel Programming | |||
Automatic Recalc | Excel Worksheet Functions | |||
Automatically paste cells that return a value, otherwise maintain existing formula. | Excel Programming | |||
Automatically paste cells that return a value, otherwise maintain existing formula. | Excel Programming | |||
recalc question | Excel Programming |