stop formula recalculation when formula contains UDF

E

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
 
D

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
=
 
C

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top