Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Today - volatile function
I use Today() in my worksheet. It is a volatile function and since I have a
number of cells dependent on it, it may be one reason for slow calculation in my workbook. I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Today - volatile function
The TODAY function shouldn't be slowing down your workbook. Normal reasons
for slow workbooks are the foillowing 1) workbooks with lots of macros or macros that take a long time to run 2) workbooks with lots of formulas especially the function SUMPRODUCT. 3) worksbooks that contain links to closed workbooks. "dhstein" wrote: I use Today() in my worksheet. It is a volatile function and since I have a number of cells dependent on it, it may be one reason for slow calculation in my workbook. I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Today - volatile function
I have a number of cells dependent on it
It depends on how many cells are dependent upon it. Try what you want to do and see if it makes a difference. -- Biff Microsoft Excel MVP "dhstein" wrote in message ... I use Today() in my worksheet. It is a volatile function and since I have a number of cells dependent on it, it may be one reason for slow calculation in my workbook. I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Today - volatile function
"dhstein" wrote:
I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. I think you are suggesting calling TODAY() in one cell, then having a "start-up macro" (workbook_open event macro) copy that cell to another cell. If I understand you correctly, that is no better nor worse than simply having the workbook_open event macro write the result of the VBA Date function into the location referenced by dependent cells. In either case, the downside is that opening the workbook will result in an "enable macro?" prompt (assuming a prudent macro security level), which may be an unnecessary nuisance unless you have other macros. You might as well simply call TODAY() in the one cell referenced by dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? "No" to the second question; "probably not" to the first question. Even if you simply write the result of the VBA Date function into a cell instead of calling TODAY(), the change to that cell will cause all dependent cells to be recalculated. So there is no savings at all. Moreover, I doubt that replacing many calls to TODAY() with one call and dependent references will speed up calculations significantly. First, each instance of TODAY() is called only one time for every calculation cycle. So the number of cells calculated does not matter, whether or not they reference the cell with TODAY(). You can prove this by having a cell with the formula =myfunc(1)+today(), where MYFUNC() is: function myfunc(val) msgbox "myfunc " & val end function Use a different parameter for each cell that calls MYFUNC(). Second, even if you do have a "huge" number of calls to TODAY() now, reducing that to one call directly or indirectly (via VBA) with many other dependent cells is not likely to have a significant effect on calculation time unless the total TODAY() call time accounts for a significant percentage of the total calculation time. That seems unlikely; but of course, only a test will tell for sure. ----- original message ----- "dhstein" wrote in message ... I use Today() in my worksheet. It is a volatile function and since I have a number of cells dependent on it, it may be one reason for slow calculation in my workbook. I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Today - volatile function
Joel, Biff,
Thanks for the answers. "Joel" wrote: The TODAY function shouldn't be slowing down your workbook. Normal reasons for slow workbooks are the foillowing 1) workbooks with lots of macros or macros that take a long time to run 2) workbooks with lots of formulas especially the function SUMPRODUCT. 3) worksbooks that contain links to closed workbooks. "dhstein" wrote: I use Today() in my worksheet. It is a volatile function and since I have a number of cells dependent on it, it may be one reason for slow calculation in my workbook. I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Today - volatile function
Joe,
Thanks for the response "JoeU2004" wrote: "dhstein" wrote: I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. I think you are suggesting calling TODAY() in one cell, then having a "start-up macro" (workbook_open event macro) copy that cell to another cell. If I understand you correctly, that is no better nor worse than simply having the workbook_open event macro write the result of the VBA Date function into the location referenced by dependent cells. In either case, the downside is that opening the workbook will result in an "enable macro?" prompt (assuming a prudent macro security level), which may be an unnecessary nuisance unless you have other macros. You might as well simply call TODAY() in the one cell referenced by dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? "No" to the second question; "probably not" to the first question. Even if you simply write the result of the VBA Date function into a cell instead of calling TODAY(), the change to that cell will cause all dependent cells to be recalculated. So there is no savings at all. Moreover, I doubt that replacing many calls to TODAY() with one call and dependent references will speed up calculations significantly. First, each instance of TODAY() is called only one time for every calculation cycle. So the number of cells calculated does not matter, whether or not they reference the cell with TODAY(). You can prove this by having a cell with the formula =myfunc(1)+today(), where MYFUNC() is: function myfunc(val) msgbox "myfunc " & val end function Use a different parameter for each cell that calls MYFUNC(). Second, even if you do have a "huge" number of calls to TODAY() now, reducing that to one call directly or indirectly (via VBA) with many other dependent cells is not likely to have a significant effect on calculation time unless the total TODAY() call time accounts for a significant percentage of the total calculation time. That seems unlikely; but of course, only a test will tell for sure. ----- original message ----- "dhstein" wrote in message ... I use Today() in my worksheet. It is a volatile function and since I have a number of cells dependent on it, it may be one reason for slow calculation in my workbook. I'm thinking about having the function in one cell and running a startup macro to copy the value to another location and using that for all dependent cells. Will that help with calculation speed, or since the Today() function still exists in the workbook, it will not change anything? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Today() function | Excel Worksheet Functions | |||
is MATCH a volatile function? | Excel Discussion (Misc queries) | |||
why is the INDIRECT function volatile? | Excel Worksheet Functions | |||
How can I get "=TODAY()" results to be non-volatile? | Excel Worksheet Functions | |||
TODAY Function | Excel Worksheet Functions |