Making NOW() "less" volatile(?)

[d0rian]

I'm trying to rid my file of all VOLATILE functions; the only one remaining is a single NOW() function, however there are a few hundred cells who depend on that Now() value, so my understanding is that even though only on cell is technically 'volatile', I've effectively turned it into several hundred volatile cells because each of those dependents is going to get recalculated each time NOW() changes (which is literally every second, right?)

So 2 Q's:

1) Am I correct in my understanding that even though I'm only using a single volatile function in a single cell, Excel's dependency tree means each of the several hundred dependent cells are now being forced to recalculate every second, thus essentially rendering them all 'volatile' too?

2) Is it possible to instruct Excel to perhaps only recalculate the current time -- i.e. the NOW() value -- every, say, 60 seconds? Essentially that would reduce the decimal 'precision' of NOW() from on-the-second to on-the-minute.

 

[Rick Rothstein]

So 2 Q's:

1) Am I correct in my understanding that even though I'm only using a single volatile function in a single cell, Excel's dependency tree means each of the several hundred dependent cells are now being forced to recalculate every second, thus essentially rendering them all 'volatile' too?

2) Is it possible to instruct Excel to perhaps only recalculate the current time -- i.e. the NOW() value -- every, say, 60 seconds? Essentially that would reduce the decimal 'precision' of NOW() from on-the-second to on-the-minute.

No, the Volatile NOW function does not recalculate every second... it does not recalculate unless the worksheet it is on recalculates. What it means for a function to be Volatile is that it will recalculate when ever the worksheet recalculates even if that function is not involved in whatever forced the recalculation. In other words, if you had this formula in a cell...

=A1*B1

and the value in A1 changes, not only does the cell with the above formula change, but every cell containing a Volatile function updates at the same time even though the change that forced the recalculation did not involve any of the cells containing Volatile functions. So, for your last question... there is nothing you need to do.

While it is true that every cell referencing your NOW formula will recalculate, I believe that is still easier on Excel than if you put NOW into each of those formulas directly. I don't know the underlying mechanism that make this so, but that is what people who know a lot more than I do about formulas say... so I believe them.

 

[d0rian]

Thanks for the reply. I suppose it's then important to add that my worksheet is recalculating every second because it's streaming in real-time stock quotes via my brokerage's API. So I've got hundreds of cells that are pulling in new tick-by-tick stock data each second, which I presume would be more than enough to qualify as a 'recalculation' that would trigger the recalculation of every cell containing a Volatile function (and its dependents).

My file is often very laggy/sluggish, and in trying to troubleshoot it, I've read that Volatile functions are a prime culprit, hence I'm trying to get rid of those wherever possible. This NOW() function (and its few hundred dependents) are my last one to get rid of...

 

[joeu2004]

I believe that is still easier on Excel than if you put NOW into each of those formulas directly. I don't know the underlying mechanism that make this so, but that is what people who know a lot more than I do about formulas say... so I believe them.

It might be "easier on Excel", but it is less reliable. Do the following experiment and let us know what you learn.

Put the following formula into A1: =NOW()=NOW()

Then create and execute the following VBA procedure:

Sub doit()
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 100000
Range("a1").Calculate
If Not Range("a1") Then Exit For
Next
Application.ScreenUpdating = True
MsgBox i
End Sub

This leaves A1 set to FALSE every time I execute it on my computer, with varying number of iterations.

The failure can occur when the current time is after 63/64 of a second [1]. The system clock "tick" might interrupts the two NOW calls, resulting in two different times of day.

[1] This presumes that the system clock is updated every 1/64 second (15.625 milliseconds). That is the default behavior for Windows. Some applications change the frequency. So it would be prudent to close extraneous application during the experiment. But usually, the applications increase, not decrease, the frequency. So they should not impact the outcome, except to make it more likely simply because there are more processes to run in addition to the Excel and VBA threads.

This can cause havoc in Excel files where multiple time-dependent conditional formulas have inconsistent results because NOW or TODAY is called in each formula.

(A problem with TODAY occurs much less often, of course, namely only after 63/64 of a second just before midnight.)

 

[Rick Rothstein]

I may be missing something... please clarify.

It might be "easier on Excel", but it is less reliable.

This sounds like you are arguing to put NOW or TODAY in each formula rather than having them call a single cell containing NOW or TODAY.

This can cause havoc in Excel files where multiple time-dependent conditional formulas have inconsistent results because NOW or TODAY is called in each formula.

This sounds like you are arguing not to put NOW or TODAY in each formula.

 

[joeu2004]

Is it possible to instruct Excel to perhaps only recalculate the current time -- i.e. the NOW() value -- every, say, 60 seconds?

Yes.

When I want to control "volatile" calculations manually, I put them into a separate worksheet, and use VBA procedures to manually disable and enable worksheet calculation.

However, if you want to do something "every 60 seconds", you must use Application.OnTime in VBA to control the frequency.

So you might as well use the VBA function Now instead of the Excel function NOW. The following is a skeletal design.

Create the following in a normal VBA module (Insert > Module):

Dim nextTime As Date

Sub repeatit()
Range("A1") = Now
nextTime = Now + TimeSerial(0, 0, 2)
Application.OnTime nextTime, "repeatit"
End Sub

Sub stopit()
On Error Resume Next
Application.OnTime nextTime, "repeatit", , False
End Sub

For demonstrations purpose, I use a 2-second frequency. You would use TimeSerial(0,1,0).

Execute "repeatit" and watch A1 in the Formula Bar. You should see that it updates every 2 seconds.

Execute "stopit" to stop the time updates.

It is prudent to also create the following event macro in the ThisWorkbook object (press ctrl+R to open the Project Explorer, double-click ThisWorkbook):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
stopit
End Sub

This stops the time updates if you close the workbook without exiting Excel.

To demonstrate, comment out or remove the "stopit" line. Execute "repeatit". Then close the workbook without exiting Excel.

You will see that within 2 seconds, the workbook is reopened, and A1 continues to be updated. The reason is: the OnTime event is still scheduled.

Execute "stopit" to stop the time updates.

In contrast, with the "stopit" line in place, execute "repeatit", and close the workbook without exiting Excel.

You will see that the workbook is not reopened because we stopped the OnTime event just before closing the workbook.

 

[joeu2004]

I may be missing something... please clarify. [....] This sounds like you are arguing to put NOW or TODAY in each formula [....] This sounds like you are arguing not to put NOW or TODAY in each formula.

I think my statements were clear and non-contradictory.

Be that as it may, putting nitpicking aside, I think the experiment that I presented should clarify any misunderstanding.

It demonstrates that calling Excel NOW multiple times, even in the same formula, can lead to inconsistent times during the same recalculation cycle.

Isn't that what you learned when you tried the experiment?

-----

PS.... I wrote: ``The failure can occur when the current time is after 63/64 of a second``.

Actually, it can happen almost every 1/100 second (based on the default system clock tick frequency).

I forgot that Excel NOW is truncated to the 1/100 second, whereas VBA Now is truncated to the second.

To demonstrate, put =NOW() into both A1 and A2 formatted as m/dd/yyyy hh:mm:ss.000 . In the VBA procedure, change

If Not Range("a1") Then Exit For

to

If Range("a1") <> Range("a2") Then Exit For