Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculation of a custom function in excel
I've created a custom function in Excel 2003 to meet a need that's too
complicated to be feasible in a formula. When I initially create the formula that references my function, the cell containing the formula calculates correctly. However, when the underlying data changes, the output of the formula does not change. Manually triggering a recalc of the worksheet via F9, CNTL+SHIFT+ALT+F9, etc. don't update the result either. I know that the formula is not recalcing, because I've got a breakpoint in my function, and the VBA is not being called. The only time that the VBA executes is when a formula that calls the function is first created. Is there something special about recalculating cells containing custom functions that affects how they recalculate? Is there something else I need to be doing? Thanks, Mark |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculation of a custom function in excel
put the following line of code at the beginning of your custom function:
Application.Volatile -- Kevin Backmann "Mark VII" wrote: I've created a custom function in Excel 2003 to meet a need that's too complicated to be feasible in a formula. When I initially create the formula that references my function, the cell containing the formula calculates correctly. However, when the underlying data changes, the output of the formula does not change. Manually triggering a recalc of the worksheet via F9, CNTL+SHIFT+ALT+F9, etc. don't update the result either. I know that the formula is not recalcing, because I've got a breakpoint in my function, and the VBA is not being called. The only time that the VBA executes is when a formula that calls the function is first created. Is there something special about recalculating cells containing custom functions that affects how they recalculate? Is there something else I need to be doing? Thanks, Mark |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculation of a custom function in excel
You could put
application.Volatile as the first command. but this would cause it to always recalculate Excel determines dependencies by looking at the argument list of the function. Put all the precedent ranges in the argument list and it should recalculate appropriately. -- Regards, Tom Ogilvy "Mark VII" wrote: I've created a custom function in Excel 2003 to meet a need that's too complicated to be feasible in a formula. When I initially create the formula that references my function, the cell containing the formula calculates correctly. However, when the underlying data changes, the output of the formula does not change. Manually triggering a recalc of the worksheet via F9, CNTL+SHIFT+ALT+F9, etc. don't update the result either. I know that the formula is not recalcing, because I've got a breakpoint in my function, and the VBA is not being called. The only time that the VBA executes is when a formula that calls the function is first created. Is there something special about recalculating cells containing custom functions that affects how they recalculate? Is there something else I need to be doing? Thanks, Mark |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculation of a custom function in excel
Thanks for the suggestion. Application.Volatile did the trick.
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avoiding Recalculation for a function in automatic mode | Excel Worksheet Functions | |||
weird recalculation of user defined function | Excel Worksheet Functions | |||
Recalculation of User Function when Hiding arguments | Excel Programming | |||
Recalculation on cell changes fails when using VBA function | Excel Programming | |||
How to force a recalculation of values based on custom function | Excel Programming |