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...
--
HTH...

Jim Thomlinson


"joeu2004" wrote:

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