SheetChange fire UDF unintentionally

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Peter - 21 Mar 2006 12:30 GMT

Dears,

I just created a UDF (with 4 parameters) which can return a value.

Moreover, I code in the SheetChange event (after this UDF being fired) and
change the last parameter in this UDF cell to another value, however, the UDF
fire again BEFORE my SheetChange event end unexpectedly. I tried to set
EnableEvent = false and Application.Calculation = Manual inside the
SheetChange handler, but it doens't work and the UDF will be fired when it
run to "xx.Formula = "=myFunc(a, b, c, d) <- d is a new value and modified by
myself.

Is it impossible for me to do such task?

Thanks,

Reply to this Message

Charles Williams - 21 Mar 2006 14:49 GMT

Any time you enter/change a formula (using keyboard or VBA), even in Manual
calculation mode, the formula gets evaluated, so your UDF fires.

why not make D a reference to a cell and change the cell rather than the UDF
parameter?

Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

> Dears,
>
[quoted text clipped - 13 lines]
>
> Thanks,

Reply to this Message

Peter - 21 Mar 2006 15:12 GMT

Hi Charles,

Thanks for your reply!

Your suggestion make sense, but my boss want the formula can be self-sustain
and portable. Since the formula can be update itself, and thus user can
Re-calc later on, but storing the parameter D in a cell reference create
unnecessary dependence to other cell, which maybe removed by users (apart
from the hidden sheet approach)

Do you know how can I freeze the calculation/ application events? I tried
Application.Calculation -> Manual AND/OR Application.EnableEvents -> false
without luck...

Thanks for any idea!

> Any time you enter/change a formula (using keyboard or VBA), even in Manual
> calculation mode, the formula gets evaluated, so your UDF fires.
[quoted text clipped - 25 lines]
> >
> > Thanks,

Reply to this Message