weird recalculation of user defined function

[timspier]

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?

[Harlan Grove]

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.

[Bob Phillips]

Why not use Data Validation?

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"timspier" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Harlan Grove]

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.