On Feb 11, 11:07 pm, Jim Thomlinson wrote:
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 -----
On Feb 11, 11:07*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com wrote:
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