On Feb 12, 1:28 am, "Charles Williams"
wrote:
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 -----
On Feb 12, 1:28*am, "Charles Williams"
wrote:
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 seehttp://www.decisionmodels.com/calcsecretsc.htm
For more info on UDFs recalculating more than once seehttp://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 Grouphttp://www.decisionmodels.com/OZEUC.htm
"joeu2004" wrote in message
...
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). *D1:D45 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