VBA Functions accessing value from worksheet

[sachinshah]

Hi,

I have defined a function 'calculateIntrest' in a module in an excel
file. This function refers value in a different sheet say
'MasterDataSheet'. A Sheet say 'DataSheet' uses this fucntion to
calculate interest amount. Rate of interest is stored in
'MasterDataSheet' which is being referred by function
calculateIntrest(). Below is my problem.

I have Rate of Interest as 1.50 defined. Now considering this value, i
have entered data in 'DataSheet' and corrosponding calculations are
done using rate of interest as 1.50. Once my data entry is over i
change this Rate of interest from 1.50 to 2.0. Problem is EXCEL does
not recalculate the values in interest column unless i manually do F2
and enter. I understand as i am referring the value of
MasterDataSheet!RateOfInt from within my function calculateInterest it
is not updating automatically. But then how do update these values
automatically everytime RateOfInterest in MasterDataSheet is changed.

Any help will be greatly appreciated.

Sachin

 

[Tom Ogilvy]

You could make it volatile

in the first line of the function add
Application.Volatile

the better alternative is to make all ranges as arguments to your function.
Any change to a cell in the argument list would cause Excel to recalculate
the funciton.