Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange fire UDF unintentionally
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, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange fire UDF unintentionally
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 "Peter" wrote in message ... 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, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SheetChange fire UDF unintentionally
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! "Charles Williams" wrote: 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 "Peter" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
unintentionally changing SS | Excel Discussion (Misc queries) | |||
how to handle 'sheetchange' event on 'add-in' | Excel Programming | |||
SheetChange Event | Excel Programming | |||
SheetChange Event | Excel Programming | |||
XLapp and SheetChange | Excel Programming |