Today - volatile function

[dhstein]

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?

 

[JoeU2004]

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]

Joel, Biff,

Thanks for the answers.

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]

Joe,

Thanks for the response

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.



"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 -----