weird recalculation of user defined function
 

I created a small UDF which returns a value if the inputs are valid, but
if the inputs are not in the correct range it posts a messagebox with an
error message. If I copy this to a range of cells down a column, it
works great. If I insert a blank row somewhere in the range, no
problem. However, if I then DELETE the blank row, or any other row, all
the functions appear to be recalculated because I get a couple of error
messageboxes corresponding to the couple of inputs that are out of
range. I tried setting the volatile method to false, but no luck. Any
ideas what is going on?


--
timspier
------------------------------------------------------------------------
timspier's Profile: http://www.excelforum.com/member.php...o&userid=32090
View this thread: http://www.excelforum.com/showthread...hreadid=541840

weird recalculation of user defined function
 

timspier wrote...
I created a small UDF which returns a value if the inputs are valid, but
if the inputs are not in the correct range it posts a messagebox with an
error message. If I copy this to a range of cells down a column, it
works great. If I insert a blank row somewhere in the range, no
problem. However, if I then DELETE the blank row, or any other row, all
the functions appear to be recalculated because I get a couple of error
messageboxes corresponding to the couple of inputs that are out of
range. I tried setting the volatile method to false, but no luck. Any
ideas what is going on?

It's poor design to have udfs called from cell formulas display
dialogs. You're now seeing why. Much better for your udf to return an
error value, though something other than #VALUE! would be a good idea
because runtime errors in udfs return #VALUE! errors, then use
Calculate event handlers to check for cells containing these udfs and
evaluating to error values.

If your copied formulas had also had invalid values, they would also
have displayed dialogs when first pasted into cells. Also, if inserting
cells had lead to some of the udf calls having invalid values, that
would also have displayed dialogs. There's nothing special about
deleting cells, it's just that doing so caused some invalid values.

Anything that triggers recalc could affect the cell formulas calling
your udf and force its reevaluation. If the formulas calling your udf
also call other volatile functions, there's no way to prevent
reevaluating your udf except by setting calculation to manual.

weird recalculation of user defined function
 

Why not use Data Validation?

http://www.contextures.com/xlDataVal01.html

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"timspier" wrote in
message ...

I created a small UDF which returns a value if the inputs are valid, but
if the inputs are not in the correct range it posts a messagebox with an
error message. If I copy this to a range of cells down a column, it
works great. If I insert a blank row somewhere in the range, no
problem. However, if I then DELETE the blank row, or any other row, all
the functions appear to be recalculated because I get a couple of error
messageboxes corresponding to the couple of inputs that are out of
range. I tried setting the volatile method to false, but no luck. Any
ideas what is going on?


--
timspier
------------------------------------------------------------------------
timspier's Profile:
http://www.excelforum.com/member.php...o&userid=32090
View this thread: http://www.excelforum.com/showthread...hreadid=541840

weird recalculation of user defined function
 

Bob Phillips wrote...
Why not use Data Validation?
....

Data validation doesn't work with formulas. It also doesn't work when
pasting into cells with data validation rules. Other than that it's
great.