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 |
Why is my vbasum(F1:F45) called 46 times?
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 |
Why is my vbasum(F1:F45) called 46 times?
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 |
Why is my vbasum(F1:F45) called 46 times?
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 |
Why is my vbasum(F1:F45) called 46 times?
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 |
All times are GMT +1. The time now is 10:27 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com