×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

How to disable UDF while executing other sub

How to disable UDF while executing other sub

How to disable UDF while executing other sub

(OP)
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!

RE: How to disable UDF while executing other sub

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/
 

RE: How to disable UDF while executing other sub

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

RE: How to disable UDF while executing other sub

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/
 

RE: How to disable UDF while executing other sub

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.

RE: How to disable UDF while executing other sub

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources