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

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

recalculation of a custom function in excel
 

You should always post your code for comments.
Did you put
application.volatile
as the first line in the function?
You may need =now() somewhere in an unused cell.

--
Don Guillett
SalesAid Software

"Mark VII" wrote in message
...
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

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

recalculation of a custom function in excel
 

Thanks for the suggestion. Application.Volatile did the trick.