SheetChange fire UDF unintentionally

[Guest]

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,

 

[Charles Williams]

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

 

[Guest]

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!