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