Faster to use only one cell with a volatile function and have other cells access that cell?

[Mr. Snrub]

Right now I have lots of cells in my table that use the NOW function, which is volatile. Would it be faster to just have one cell outside of my table that would store the value of NOW and then have all the other cells reference that cell address in their calculations, or would that be a waste of time?

 

[RoryA]

There is no definite answer to that as it will depend on the formulas to some extent. A cell containing a volatile function like NOW() will be treated as volatile even if the function could never be called, whereas if you refer to a cell containing NOW instead, the formula cell would not be considered volatile. Personally, I would use a single NOW cell and refer to that.

 

[joeu2004]

Right now I have lots of cells in my table that use the NOW function, which is volatile. Would it be faster to just have one cell outside of my table that would store the value of NOW and then have all the other cells reference that cell address in their calculations, or would that be a waste of time?

There are two questions to ask: performance and, more importantly, correctness.

With respect to NOW (and TODAY) per se, it is more correct to enter it into one cell and reference that cell everywhere else. There is an unusual (and yes, unlikely) situation where multiple calls to NOW returns different times. This can happen during the last 15.625 msec of the second. See the demonstration below.

With respect to performance, it might be slightly (and probably imperceptibly) faster to call the function once in one cell and reference that cell everywhere else. There is a trade-off between the execution time of multiple calls to the function and the internal processing time for the increased number of cell dependencies.

(By the way, in conditional statements, the explicit function call might not be executed every time. But if there a cell reference, it impacts the cell dependency processing every time, even if the reference to the cell with the volatile function call is not executed.)

But that depends on the complexity of the function call. For example, multiple calls to a VLOOKUP of 1+ million rows probably takes a lot longer than one call and multiple references. Of course, that is not an issue with NOW per se. But it might be an issue with some designs that use OFFSET and INDIRECT with embedded costly function calls.

In any case, please note: any cell that references a cell with a volatile function reference is also recalculated whenever the "volatile cell" is recalculated. In effect, even a cell that references a "volatile cell" is itself volatile.

-----

PS.... Forgot to add the demonstration.

Enter the formula =NOW() into A1 and A2. Then execute the following macro. On my computer, it displays the "error" 2 out of 3 times. YMMV.

Sub doit()
Dim i As Long
Application.ScreenUpdating = False
For i = 1 To 10000
    Application.Calculate
    If Range("a1") <> Range("a2") Then Exit For
Next
Application.ScreenUpdating = True
If i > 10000 Then MsgBox "okay" Else MsgBox i & "  error"
End Sub

 

[joeu2004]

A cell containing a volatile function like NOW() will be treated as volatile even if the function could never be called, whereas if you refer to a cell containing NOW instead, the formula cell would not be considered volatile.

I believe that is incorrect. Consider the following example....

Create the UDF below. Then enter the following formulas:

A3: =IF(FALSE,NOW(),0)
B3: =IF(ISNUMBER(A3),myudf(TRUE),FALSE)

Note that NOW() is never called. Moreover, the result of ISNUMBER(A3) is never changed (always TRUE).

Nevertheless, "myudf" is called every time that we press F9.

The UDF....

Function myudf(x)
myudf = x
MsgBox "myudf"
End Function

 

[RoryA]

In effect, even a cell that references a "volatile cell" is itself volatile.

Not exactly. Say you have =NOW() in A1, and TRUE in B1. Then in A2 you have:
=IF(B1,some_udf(),NOW())
and in A3 you have:
=IF(B1,some_udf(),A1)

A2 will actually be volatile - changing other cells will cause the UDF to calculate.
A3 will not be volatile. It will only recalculate if B1 changes.

 

[RoryA]

I believe that is incorrect. Consider the following example....

Create the UDF below. Then enter the following formulas:

A3: =IF(FALSE,NOW(),0)
B3: =IF(ISNUMBER(A3),myudf(TRUE),FALSE)

Note that NOW() is never called. Moreover, the result of ISNUMBER(A3) is never changed (always TRUE).

Nevertheless, "myudf" is called every time that we press F9.

The UDF....

Function myudf(x)
myudf = x
MsgBox "myudf"
End Function

Yes, but A3 is volatile and is therefore recalculating every time, which is what causes the UDF to recalc.

 

[joeu2004]

if you refer to a cell containing NOW instead, the formula cell would not be considered volatile.

In effect, even a cell that references a "volatile cell" is itself volatile.

Not exactly.

Yes, but A3 is volatile and is therefore recalculating every time, which is what causes the UDF to recalc.

The point is: B3 "refer to a cell containing NOW", so it is "considered volatile" insofar as it is recalculated every time, contrary to your assertion.

Nevertheless, I agree with your counter-example. Your A3 "references a volatile cell", but it is not "itself volatile" insofar as it is not recalculated every time, contrary to my assertion.

I think it is a case of both of us saying it wrong. I believe the correct statement is: if a cell executes a reference to a "volatile cell", the referencing cell behaves as if it is volatile as well.

 

[RoryA]

I would argue that that is not behaving like a volatile cell but like any other cell whose direct precedent is recalculating.

 

[joeu2004]

I would argue that that is not behaving like a volatile cell but like any other cell whose direct precedent is recalculating.

Yes, you are right. Too bad this forum does permit deleting incorrect postings or at least late edits. It would be nice to eliminate my misdirections to avoid confusion.

 

[RoryA]

I always think it's nice to be able to see the whole discussion from start to finish, to see all the sides that were put forward.