Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Recalculation when using VB functions??
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Recalculation when using VB functions??
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Recalculation when using VB functions??
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Recalculation when using VB functions??
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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Recalculation when using VB functions??
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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Recalculation when using VB functions??
Sorry... I misunderstood your question. I really don't know why th
"automatic calculation" setting wouldn't do the trick... Sorry - Piku -- Message posted from http://www.ExcelForum.com |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Recalculation when using VB functions??
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 "Bill Buckner" schrieb im Newsbeitrag ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Balance sheet functions? | Excel Discussion (Misc queries) | |||
Stop and start Sheet recalculation | Excel Discussion (Misc queries) | |||
sheet! functions | Excel Worksheet Functions | |||
Spread sheet Functions | Excel Worksheet Functions | |||
Worksheet - Functions from sheet to sheet | Excel Discussion (Misc queries) |