How to disable UDF while executing other sub

[yakpol]

I have a spreadsheet with a few user-defined function used in the worksheets. To speed up execution of other routins I need to temporarily disable UDF.

Thanks for your help!

 

[IDS]

Application.Calculation = xlCalculationManual


I haven't checked, but surely that switches off recalculation of all functions, including UDFs.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[yakpol]

Doug,
I cannot turn off all calculations,I still rely on worksheet formulas to do most of the work.

 

[IDS]

OK, in that case how about having a worksheet cell named "RecalcFlag" then something like:

Function CalcIf(...)
If Range("RecalcFlag").Value = 1 Then

UDF code

Else
CalcIf = Application.Caller.Value
End If
End Function


Then you can set RecalcFlag to 0 whenever you don't want the UDF to recalc, but still show the original value. If you use that you will have to allow circular references (otherwise you get a warning message the first time any cell referenced by the function recalculates). I can't see a way to avoid that at the moment.

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[BigInch]

I seem to have the idea that a user function does not have to be defined with a global scope. If so, define it only in the sub where it is used.

What would you be doing, if you knew that you could not fail? Ans. Bonds and derivative brokering.

 

[electricpete]

How adjusting security settings to disallow macros....does that disable udf's?

=====================================
(2B)+(2B)' ?