prevent UDF from executing

[masterphilch]

Hi

I did some UDF on a worksheet. In some cases, I want to prevent
executing the UDF, even if an argument changed. This, just because some
processec take too much time. Is that possible?

thanks a lot for help

masterphilch

 

[Chip Pearson]

The only way to do that would be to put in an argument that tells
the UDF to return some value (e.g., #NA or 0 or "") without doing
the real calculation.


Public Function MyUDF(<your regular arguments>, _
Optional NoCalc As Boolean = False)
If NoCalc = True Then
MyUDF = CVErr(xlErrNA)
Exit Function
End If
' rest of your code
End Function


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Bill Martin]

Hi

I did some UDF on a worksheet. In some cases, I want to prevent
executing the UDF, even if an argument changed. This, just because some
processec take too much time. Is that possible?

thanks a lot for help

masterphilch

--------------------------

What I have done where I have a UDF which is not required 99.9% of the time is
to create 2 macros I can execute from a button on the spreadsheet. One of them
cycles through all the worksheets and replaces all instances of the function in
cells with its current result as a fixed number. The other one cycles through
the worksheets and puts the function back into the proper cells if I actually
need it.

There may well be a more elegant approach.

Bill

 

[masterphilch]

that's what I thought...

I'll try to handle that somehow else...

thx

 

[masterphilch]

--------------------------

What I have done where I have a UDF which is not required 99.9% of the time is
to create 2 macros I can execute from a button on the spreadsheet. One of them
cycles through all the worksheets and replaces all instances of the function in
cells with its current result as a fixed number. The other one cycles through
the worksheets and puts the function back into the proper cells if I actually
need it.

There may well be a more elegant approach.

Bill

Hi

thanks for your reply!

I already thought about clear the cells in which the UDF is written and
reenter the content, when needed. I need to think, what's really needed.

thanks anyway!