Why is my vbasum(F1:F45) called 46 times?

[joeu2004]

Why is my VBA function vbasum(F1:F45) called 46 times?

More to the point, how can I avoid it?

I expected that Excel would call vbasum() only once after all
dependencies (F1:F45) had been calculated.

Instead, vbasum() seems to be called after each cell in the range
F1:F45 is calculated, and one time before the first cell in the range
is calculated (F1:F45 are all zero then).

I list vbasum() below.

It might be useful to know something of worksheet design. Each of the
cells in F1:F45 contains a formula of the form (in F1)
=VALUE(D1&":"&E1). D145 contains =RANDBETWEEN(0,23), and E1:E45
contains =RANDBETWEEN(1*(D1=0),59). Of course, when these formulas
are copied down rows 2:45, D1 and E1 become D2 and E2, etc.

H3 contains =vbasum(F1:F45). I cause recalculation by selecting H3,
pressing F2, then pressing Enter.

I am using Office Excel 2003 with VB 6.3.

-----

Option Explicit
Private cnt As Long

Private Sub initcnt()
cnt = 0
End Sub

Function vbasum(rng As Range) As Double
Dim cell As Range
Dim first As Long
cnt = cnt + 1
Debug.Print "----- vbasum #"; cnt; Date; Time
vbasum = 0
For Each cell In rng
vbasum = vbasum + cell
If first < 5 And cell > 0 Then
' display the first 5 non-zero cells
first = first + 1
Debug.Print cell.Address; cell; vbasum
End If
Next cell
Debug.Print "vbasum #"; cnt; vbasum
End Function

 

[Jim Thomlinson]

randbetween is a volatile function which means that it will recalc when any
calculation occurs anywhere in the application. You have 45 instances of
Randbetween referencing a formula with randbetween in it. That will cause 46
recalculations...

 

[Charles Williams]

Because excel recalculates using the last calculation sequence modified by
any formulae altered since the last recalc (modified formulae are calculated
first - LIFO).
So VBASUM gets recalculated first,
but Excel discovers that it depends on uncalculated cells so reschedules its
calculation for later,
then each RAND formula gets recalced followed by the dependencies of each
RAND (which is VBASUM, but it gets resheduled again until after the last
RAND)

When this has finished Excel stores the final calculation sequence
(excluding the rescheduled calls) for next time

So if you just press F9 it will only be recalculated once because VBASUM
will be in its proper place in the calculation sequence chain.

You can use ISEMPTY to check for uncalculated cells.

For more info on calculation sequence see
http://www.decisionmodels.com/calcsecretsc.htm

For more info on UDFs recalculating more than once see
http://www.decisionmodels.com/calcsecretsj.htm

To find out how to write efficient UDFs come to my class at the Sydney Excel
Users Conference!

Charles
__________________________________________________
Outlines for my Sessions at the Australia Excel Users Group
http://www.decisionmodels.com/OZEUC.htm

 

[joeu2004]

Because excel recalculates using the last calculation sequence
modified by any formulae altered since the last recalc (modified
formulae are calculated first - LIFO). So VBASUM gets recalculated
first, but Excel discovers that it depends on uncalculated cells so
reschedules its calculation for later, then each RAND formula gets
recalced followed by the dependencies of each RAND (which is
VBASUM

Thanks. I had instrumented the formulas before I saw your posting, so
I know that what you are saying is correct. And thanks for the
pointers to your web site. Your explanations there and here add some
clarity to what I had discovered with my instrumentation.

Although your explanation makes sense insofrar as it describes the
facts, it seems like a broken implementation. Excel should maintain a
directed graph (ordered tree) of cells. A proper traversal and
execution of the directed graph should result in performing the
calculations in the correct order. As you say, calculations for a
cell might be "scheduled" multiple times ; but each cell should be
evaluated only once (except for iteration among circular references,
if allowed).

To demonstrate the broken behavior, consider the following scenario.
In a new workbook, A2 contains =1+A1, B2 contains =1+A2, and B3
contains =1+B2. Press ctrl+alt+F9 in order to reach a stable state.
Edit B3. Then edit A2. In response to the last edit, we see cells
evaluated in the following order: A2, B3, B2, B3. I cannot think of
a "good" reason for evaluating B3 twice. In fact, if we edit A2 again
(but not B3), we see the expected evaluation order, namely: A2, B2,
B3. Excel corrects itself(!).

Oh well, it is what it is. But I do believe it is a defect or
improper design.

The following is how I instrumented the formulas. Since all
debug.print output goes on one line, it is important to clear the
Immediate Window before each test step to avoid confusion.

A2: =vbtrace(,1)+A1
B2: =vbtrace(,1)+A2
B3: =vbtrace(,1)+B2

Function vbtrace(Optional tag, Optional rtn)
If Not IsMissing(tag) Then
Debug.Print Application.Caller.Address; "("; Trim(tag); ") ";
Else
Debug.Print Application.Caller.Address; " ";
End If
If Not IsMissing(rtn) Then vbtrace = rtn
End Function



----- original posting -----

 

[joeu2004]

randbetween is a volatile function which means that it will recalc
when any calculation occurs anywhere in the application. You
have 45 instances of Randbetween referencing a formula with
randbetween in it. That will cause 46 recalculations.

Thanks. Your posting gave me a good idea about how to fix the
problem, namely: replace RANDBETWEEN with my own non-volatile UDF,
vbrandbetween, and use ctrl+alt+F9 to generate new random cases.

FYI, the problem has nothing to do with the 45 instances of one
RANDBETWEEN formula referencing the other (column E). It has to do
with the size of the range (F1:F45) referenced in the vbsum formula.
I had the problem before, when column E did not refer to column D.
The formulas in column E were simply =RANDBETWEEN(1,59). I realized
my defect (not allowing for zero minutes) only after I discovered the
problem with repeated evaluation of vbsum.

Instrumentation reveals, as Charles explains, that H3 (the vbsum
formula) is evaluated once for each cell in the range F1:F45. For
example, after editing H3, the first sequence of evaluations is:

F45 D45 F45 E45 F45 H3 vbsum # 2 45 2.22222222222222E-02

There are 44 others, all preceded by an initial call to vbsum.

In fact, we see that F45 (the VALUE formula) is evaluated several
times, for the same reason. If F45 had a reference to a UDF instead
of just VALUE(), the UDF would have been executed 3(!) times for each
cell in the F1:F45 range in H3.

(Indeed, the instrumented formula in F45 is =vbtrace()
+VALUE(D1&":"&E1). There is similar instrumentation in all of the
other formulas. See my response to Charels for a listing of vbtrace.
Also note: I modified the vbsum output to show the size of the range
parameter between the cnt and vbsum result.)

This is just wrong, IMHO, as I explained in my response to Charles. I
knew and expected, of course, that all of the volatile formulas would
be recalculated. But I did not expect (nor should I, IMHO) that
formulas would be re-evaluated (up to a precedent) so many times.

Oh well, it is what it is: just horrible, IMHO!


----- original posting -----