In an effort to simplify my spreadsheet, I took some of the more ornerous cell formulas and converted them to VB. In the VB function I make heavy use of the Range("xxx").Value to actualy retrieve certain cell contents

For instance
year(Range("Birth_Date_Spouse").Value

My problem is now I have to hit <ctrl<alt<F9 in order to have the worksheet recalculate. If I change a cell which is referenced by "Range" in my VB function, cells depending upon the function do not automatically recalculate. I have the calculation set to "automatic" in the options

Any ideas?

Hi
try
application.calculate
in your code

--
Regards
Frank Kabel
Frankfurt, Germany

"Bill Buckner" schrieb im
Newsbeitrag ...
In an effort to simplify my spreadsheet, I took some of the more
ornerous cell formulas and converted them to VB. In the VB function I
make heavy use of the Range("xxx").Value to actualy retrieve certain
cell contents.

For instance:
year(Range("Birth_Date_Spouse").Value)

My problem is now I have to hit <ctrl<alt<F9 in order to have the
worksheet recalculate. If I change a cell which is referenced by
"Range" in my VB function, cells depending upon the function do not
automatically recalculate. I have the calculation set to "automatic"
in the options.

Any ideas?

Frank

The function I wrote is used in a lot of cells. Will putting application.calculate in the function cause the entire workbook to be recalculated or only the cell in qquestion and any based upon it? I probably have a few hundred cells which use one of my functions. If it causes the whole workbook to get recalculated every time it is called, this calculation could last forever...

Can you explain the rules that govern when and why cells are recalculated when custom functions are involved?

Hi
sorry I misunderstood you:
One way: include the cells which are used in your function as
parameter. This way Excel knows when to re-calculate yourt function

Another way: add the line
application.volatile at the beginning of your function. This way your
function is re-calculated everytime Excel re-calculates

I would try to use the first way

--
Regards
Frank Kabel
Frankfurt, Germany

"Bill Buckner" schrieb im
Newsbeitrag ...
Frank,

The function I wrote is used in a lot of cells. Will putting
application.calculate in the function cause the entire workbook to be
recalculated or only the cell in qquestion and any based upon it? I
probably have a few hundred cells which use one of my functions. If it
causes the whole workbook to get recalculated every time it is called,
this calculation could last forever....

Can you explain the rules that govern when and why cells are
recalculated when custom functions are involved?

Well, you can create a function for the Workbook_Change event that wil
call the other functions when there is a change to the sheet. Be sur
to start the function off with

Application.EnableEvents = False

because if you don't, any changes made will retrigger the same event.
End the function with

Application.EnableEvents = True

Let me know if you need more help. - Piku

--
Message posted from http://www.ExcelForum.com