Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Functions accessing value from worksheet
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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Functions accessing value from worksheet
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. -- Regards, Tom Ogilvy "sachinshah" wrote in message ... 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 ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
accessing excel 2003 functions with excel 2007 | Excel Worksheet Functions | |||
Accessing cube functions (ie cubevalue) from VBA | Excel Worksheet Functions | |||
error accessing a protected worksheet | Excel Worksheet Functions | |||
Accessing Cells in Separate Worksheet | Excel Discussion (Misc queries) | |||
accessing CheckBox on Worksheet | Excel Programming |