Forcing Evaluation of Functions

[meryl]

I have created a function in VBA and refer to it in
various places on a worksheet - it produces correct
results when I initially hit enter on the cell. When the
data it uses changes, the cell with the function in it
does not automatically change (like it would with a
formula). If I go to the cell and hit enter in the input
area, it does correctly re-evaluation the function.

Is there a command I can run that will force it to re-
evaluate the function on the whole worksheet (without me
hitting enter on each cell).

Thanks for any suggestions,
Meryl

 

[Bob Kilmer]

Lok up Volatile Method in help.

I found this:
Example
This example marks the user-defined function "My_Func" as volatile. The
function will be recalculated whenever calculation occurs in any cells on
the worksheet on which this function appears.

Function My_Func()
Application.Volatile
'
' Remainder of the function
">
End Function

 

[Charles Williams]

Its better to put all the ranges that the function refers to as arguments
for the function: that way Excel can tell when to recalculate the function.
Using Volatile works, but the function will be recalculated at every single
calculation rather than only when it needs to be. This can be very slow.


Charles
______________________
Decision Models
FastExcel 2.1 now available
www.DecisionModels.com

 

[Meryl]

Thank you - this did the trick - did not have to worry
about the performance.