stop redundant calls to UDF
 

Hi All

I have an Excel 2003 UDF that I want called exactly once. Here's one I'll
use as an example:

public int MySum(object cells)
{
int sum = 0;
foreach(Excel.Range range in ((Excel.Range)cells))
{
sum += Convert.ToInt32(range.Value2);
}
return sum;
}

In my Excel worksheet, I have a cell containing the formula: =MySum(A1:A3),
with A1:A3 each containing the volatile function =rand()

The problem is, with the calculation mode set to Automatic, MySum gets
called a total of 4 times, one for the initial call to MySum and once for
each time a new random number is generated. I could set the calc mode to
Manual, but at some point I need to set it back to Automatic, and at that
point it will perform the extra calls to MySum.

My question is, is there anyway I can have MySum called only once, but end
up in Excel Auto calculation mode? All suggestions are greatly appreciated.
Let me know if I need to clarify anything. Thanks