Sheet Recalculation when using VB functions??

[Bill Buckner]

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 Kabel]

Hi
try
application.calculate
in your code

--
Regards
Frank Kabel
Frankfurt, Germany

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.

 

[pikus]

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

 

[Bill Buckner]

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?

 

[Frank Kabel]

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

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?

 

[pikus]

Sorry... I misunderstood your question. I really don't know why th
"automatic calculation" setting wouldn't do the trick... Sorry - Piku

 

[Frank Kabel]

Hi
yes: best way would to include all used cells as aparemeter in your
function. Don't hardcode cell references within your function

--
Regards
Frank Kabel
Frankfurt, Germany

pikus,

Not sure I understand your suggestion. The functions do not update

the entire worksheet. They are called directly by cells and are used
in the calculation of those cells. For instance:

=ROUND(calcAnnuity("Primary", A7)+calcAnnuity("Spouse", A7),0)

Where calcAnnuity is my custom function.

I don't really want the worksheet to go through a complete

recalculation every time the function is called, but I do want cells
based upon cells which call the custom function to be recalculated when
I change a value from which the custom function gets data.

Would I be better off passing the cell references an an argument to

the custom function instead of using "Range" to retrieve valuse in the
function itself? Would this be recognized as a change by Excel?

I don't really understand the recalculation rules very well,

especially when I write a custom function.