stop formula recalculation when formula contains UDF

[ep]

Hi,

I'm developing an addin for Excel 2003 in C# that loops through a range of
cells that contain formulas and, based on certain conditions, changes the
formula. The problem is, the formulas contain calls to my UDF's, so every
time I change the range.Formula property, it will jump right into my
functions and recalculate. I've tried setting the application.Calculation
method to xlCalculationManual, but it still continues to run the UDF as soon
as I set the formula property.

Is there anything else I can try? Or any reason why I should expect this
behavior?

thanks in advance

 

[Dave Peterson]

This many not work for you, but how about building all the formulas as strings,
then converting them all at once to formulas--so just one giant recalc takes
place.

mycell.formula = "$$$$$=sum(a1:x99)"

then at the end, change all the
$$$$$=
to
=

 

[Charles Williams]

Formulas are always evaluated by Excel when they are entered or changed,
thats by design, whether they are UDFs or ordinary formulae.

If you dont want your UDFs to execute then set a Global switch to True and
add
If Switch then exit function
to the top of your UDFs

Then when you have finished changing the formulae set the switch to False
and reset calculation to whatever it was before you started.

For performance reasons it is usually best to modify a block of formulae in
an array and then write it back to Excel in one block.

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm