Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Why is my vbasum(F1:F45) called 46 times?
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing Times in Text Boxes to Times in Cells | Excel Programming | |||
Calculation of hourly rate times hours times 1.5 | Excel Worksheet Functions | |||
Counting the number of times someone called in sick | Excel Discussion (Misc queries) | |||
How are relay leg times or driving times entered and totaled? | Excel Worksheet Functions | |||
Charting and analyzing Times' times data for trends | Excel Discussion (Misc queries) |