Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
"Bernd P" wrote in
Newsgroups: microsoft.public.excel.worksheet.functions Subject: how can I use randbetween without repeating numbers in a set Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT) Message-ID: See JE McGimpsey's site for a solution to this problem. http://www.mcgimpsey.com/excel/udfs/randint.html A faster and more flexible RandInt you can find he http://sulprobil.com/html/randint.html These array functions use Application.Caller to determine the size and shape of the array result to be returned. This poses a problem when they are executed from a macro using Selection.Calculate under specific conditions, explained below. At least, that is the case with my revision of Excel 2003 (11.5612.5606) and VBA 6.3 (9969, 6.4.8869), part of MS Office 2003 Sm Busn Ed. However, I do not observe a similar problem with some Excel array functions, e.g. LINEST[*]. So I am wondering: is there an alternative to Application.Caller that will work under the specific conditions that exhibit the problem? The problem might not always be apparent with Bernd's and McGimpsey's RandInt() functions due to their random nature. So for demonstration purposes, consider the array function myInts() below, which returns an array of unique integers in a deterministic manner. When it is invoked by an array formula in A1:B5, e.g. =myInts(), it returns the integers 1 through 10. However, the array formula __seems__ to misbehave, returning 1 in all 10 cells, when it is invoked by a macro that implements the paradigm described below. The operative word is "seems". In fact, the array function behaves correctly. The root cause of the problem is an anomaly (defect?) of the paradigm implemented in the testit() macro below. The root cause of the problem seems to be related to setting Application.Calculation=xlCalculationManual, executing Selection.Calculate, then restoring Application.Calculation=xlCalculationAutomatic. The problem manifests itself in the last step, setting Application.Calculation=xlCalculationAutomatic. This paradigm is common when measuring performance. For example, see http://msdn.microsoft.com/en-us/library/aa730921.aspx, Sub RangeTimer() in the section "Measuring Calculation Time". I have instrumented the testit() macro and myInts() function to make the root cause more apparent. As the instrumentation demonstrates, the problem is: when we set Application.Calculation=xlCalculationAutomatic after Selection.Calculate performed a recalculation, myInts() is invoked again from each of the cells in A1:A10 individually, not as an array. Thus, Application.Caller is a single cell, not a range; and Application.Caller.Rows.Count and Application.Caller.Columns.Count are 1 instead of the reflecting the shape of the array formula, as myInts() expects. Consequently, the array formula cannot return a set of unique integers. Instead, it always returns the first integer in the set. (But Selection.Calculate did invoke myInts() once with the array formula range, and the function did return a set of unique integers. This is evident by setting a breakpoint after Selection.Calculate.) Similarly, the RandInt() functions in an array formula will not necessarily return a set of unique integers when they are executed by the testit() macro. However, LINEST does not have such a problem when Selection is the array formula described in the footnote below. After testit() is executed, V1:W5 will contain their expected values[*], not the value of V1 repeatedly as we might expect given the analysis above. So I suspect that LINEST is using something other than (just) Application.Caller to determine the shape of the array formula that includes Application.Caller, and it is returning the result in the array that corresponds to the position of Application.Caller in the array formula. How might that be done? Alternatively, is there a way to implement the paradigm in the macro testit() so as to avoid the anomaly when Application.Calculation=xlCalculationAutomatic is restored? ----- Endnotes Private callcnt As Long Sub testit() Dim oldIter, oldCalc Debug.Print "=====" callcnt = 0 With Application oldIter = .Iteration oldCalc = .Calculation .Iteration = False .Calculation = xlCalculationManual On Error GoTo done Selection.Calculate done: .Iteration = oldIter .Calculation = oldCalc MsgBox "callcnt = " & callcnt End With End Sub Function myInts() Dim rows As Long, cols As Long, n As Long, x() As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myInts: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols ReDim x(1 To rows, 1 To cols) n = 1 For r = 1 To rows: For c = 1 To cols x(r, c) = n: n = n + 1 Next c: Next r myInts = x End With End Function Function myZero() Dim rows As Long, cols As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myZero: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols End With End Function [*] Actually, even LINEST might behave slight differently. Consider the following simplistic example. X1:X4 contains 1 through 4, Y1 contains =2*X1+5, which is copied down through Y4, and V1:W5 contains the array formula =LINEST(Y1:Y4,X1:X4,TRUE,TRUE)+myzero(). When executed normally, V4 results in #NUM!. I assume that is correct under the circumstances. But when executed using the testit() macro, V4 results in zero. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
Joe User;544898[/QUOTE Wrote: I have found that I can get array formulas to return more than one location in excel 2003 from a UDF if I know the size of the array to be returned. I simply enter the formula in the first cells, copy the formual to the destijnation cells, then select all the cells where the results are to be return and enter Shft-Cntl-enter. -- joel ------------------------------------------------------------------------ joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=149930 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
It sounds as though you may be hitting the problem outlined at
http://www.decisionmodels.com/calcsecretsj.htm Array Functions UDF's can be written as multicell array formulae that can be entered using Ctrl-Shift-Enter. The results of the array calculation are returned to the cells by assigning an array to the function. Note that Excel behaves unexpectedly when a multi-cell UDF is entered or modified and depends on volatile formulae: the UDF is evaluated once for each cell it occupies. This does not happen when the UDF is recalculated, only when it is entered or changed. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Joe User" <joeu2004 wrote in message ... "Bernd P" wrote in Newsgroups: microsoft.public.excel.worksheet.functions Subject: how can I use randbetween without repeating numbers in a set Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT) Message-ID: See JE McGimpsey's site for a solution to this problem. http://www.mcgimpsey.com/excel/udfs/randint.html A faster and more flexible RandInt you can find he http://sulprobil.com/html/randint.html These array functions use Application.Caller to determine the size and shape of the array result to be returned. This poses a problem when they are executed from a macro using Selection.Calculate under specific conditions, explained below. At least, that is the case with my revision of Excel 2003 (11.5612.5606) and VBA 6.3 (9969, 6.4.8869), part of MS Office 2003 Sm Busn Ed. However, I do not observe a similar problem with some Excel array functions, e.g. LINEST[*]. So I am wondering: is there an alternative to Application.Caller that will work under the specific conditions that exhibit the problem? The problem might not always be apparent with Bernd's and McGimpsey's RandInt() functions due to their random nature. So for demonstration purposes, consider the array function myInts() below, which returns an array of unique integers in a deterministic manner. When it is invoked by an array formula in A1:B5, e.g. =myInts(), it returns the integers 1 through 10. However, the array formula __seems__ to misbehave, returning 1 in all 10 cells, when it is invoked by a macro that implements the paradigm described below. The operative word is "seems". In fact, the array function behaves correctly. The root cause of the problem is an anomaly (defect?) of the paradigm implemented in the testit() macro below. The root cause of the problem seems to be related to setting Application.Calculation=xlCalculationManual, executing Selection.Calculate, then restoring Application.Calculation=xlCalculationAutomatic. The problem manifests itself in the last step, setting Application.Calculation=xlCalculationAutomatic. This paradigm is common when measuring performance. For example, see http://msdn.microsoft.com/en-us/library/aa730921.aspx, Sub RangeTimer() in the section "Measuring Calculation Time". I have instrumented the testit() macro and myInts() function to make the root cause more apparent. As the instrumentation demonstrates, the problem is: when we set Application.Calculation=xlCalculationAutomatic after Selection.Calculate performed a recalculation, myInts() is invoked again from each of the cells in A1:A10 individually, not as an array. Thus, Application.Caller is a single cell, not a range; and Application.Caller.Rows.Count and Application.Caller.Columns.Count are 1 instead of the reflecting the shape of the array formula, as myInts() expects. Consequently, the array formula cannot return a set of unique integers. Instead, it always returns the first integer in the set. (But Selection.Calculate did invoke myInts() once with the array formula range, and the function did return a set of unique integers. This is evident by setting a breakpoint after Selection.Calculate.) Similarly, the RandInt() functions in an array formula will not necessarily return a set of unique integers when they are executed by the testit() macro. However, LINEST does not have such a problem when Selection is the array formula described in the footnote below. After testit() is executed, V1:W5 will contain their expected values[*], not the value of V1 repeatedly as we might expect given the analysis above. So I suspect that LINEST is using something other than (just) Application.Caller to determine the shape of the array formula that includes Application.Caller, and it is returning the result in the array that corresponds to the position of Application.Caller in the array formula. How might that be done? Alternatively, is there a way to implement the paradigm in the macro testit() so as to avoid the anomaly when Application.Calculation=xlCalculationAutomatic is restored? ----- Endnotes Private callcnt As Long Sub testit() Dim oldIter, oldCalc Debug.Print "=====" callcnt = 0 With Application oldIter = .Iteration oldCalc = .Calculation .Iteration = False .Calculation = xlCalculationManual On Error GoTo done Selection.Calculate done: .Iteration = oldIter .Calculation = oldCalc MsgBox "callcnt = " & callcnt End With End Sub Function myInts() Dim rows As Long, cols As Long, n As Long, x() As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myInts: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols ReDim x(1 To rows, 1 To cols) n = 1 For r = 1 To rows: For c = 1 To cols x(r, c) = n: n = n + 1 Next c: Next r myInts = x End With End Function Function myZero() Dim rows As Long, cols As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myZero: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols End With End Function [*] Actually, even LINEST might behave slight differently. Consider the following simplistic example. X1:X4 contains 1 through 4, Y1 contains =2*X1+5, which is copied down through Y4, and V1:W5 contains the array formula =LINEST(Y1:Y4,X1:X4,TRUE,TRUE)+myzero(). When executed normally, V4 results in #NUM!. I assume that is correct under the circumstances. But when executed using the testit() macro, V4 results in zero. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
"Charles Williams" wrote:
It sounds as though you may be hitting the problem outlined at http://www.decisionmodels.com/calcsecretsj.htm [....] the UDF is evaluated once for each cell it occupies. This does not happen when the UDF is recalculated, only when it is entered or changed. Interesting web site. Thanks. But my experience with this paradigm is just the opposite: the array formula is evaluated once for each cell it occupies when the array formula range is __recalculated__. More precisely, that happens when Application.Calculation=xlCalculationAutomatic is set after Selection.Calculate is executed following setting Application.Calculation=xlCalculationManual. (Selection.Calculate also evaluates the array formula once for the range that it occupies.) (But oddly (and thankfully), that does not happen when we do this manually by modifying Tools Options Calculation.) The array formula is __not__ evaluated once for each cell it occupies after entering or modifying the array formula (manually). Instead, the array formula is evaluated once for the range that it occupies. This should be self-evident from the example that I provided. In any case, my point was: even though this does happen with a LINEST array formula as well, LINEST returns the correct values in the individual cells of the array formula, notwithstanding the fact that Application.Caller is an individual cell, not the entire array formula range, when the LINEST formula is evaluated once for each cell that it occupies. How can we mimic LINEST in a UDF? That is, how can we determine the entire range of an array formula, when the UDF is called once for each cell that it occupies, and return the value of the array formula that corresponds to the position of Application.Caller in the array formula range? I tried looking at properties of Application.Caller.CurrentArray, to no avail. In particular, .Rows, .Columns and .Address still reflect the single cell, not the entire range. ----- original message ----- "Charles Williams" wrote in message ... It sounds as though you may be hitting the problem outlined at http://www.decisionmodels.com/calcsecretsj.htm Array Functions UDF's can be written as multicell array formulae that can be entered using Ctrl-Shift-Enter. The results of the array calculation are returned to the cells by assigning an array to the function. Note that Excel behaves unexpectedly when a multi-cell UDF is entered or modified and depends on volatile formulae: the UDF is evaluated once for each cell it occupies. This does not happen when the UDF is recalculated, only when it is entered or changed. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Joe User" <joeu2004 wrote in message ... "Bernd P" wrote in Newsgroups: microsoft.public.excel.worksheet.functions Subject: how can I use randbetween without repeating numbers in a set Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT) Message-ID: See JE McGimpsey's site for a solution to this problem. http://www.mcgimpsey.com/excel/udfs/randint.html A faster and more flexible RandInt you can find he http://sulprobil.com/html/randint.html These array functions use Application.Caller to determine the size and shape of the array result to be returned. This poses a problem when they are executed from a macro using Selection.Calculate under specific conditions, explained below. At least, that is the case with my revision of Excel 2003 (11.5612.5606) and VBA 6.3 (9969, 6.4.8869), part of MS Office 2003 Sm Busn Ed. However, I do not observe a similar problem with some Excel array functions, e.g. LINEST[*]. So I am wondering: is there an alternative to Application.Caller that will work under the specific conditions that exhibit the problem? The problem might not always be apparent with Bernd's and McGimpsey's RandInt() functions due to their random nature. So for demonstration purposes, consider the array function myInts() below, which returns an array of unique integers in a deterministic manner. When it is invoked by an array formula in A1:B5, e.g. =myInts(), it returns the integers 1 through 10. However, the array formula __seems__ to misbehave, returning 1 in all 10 cells, when it is invoked by a macro that implements the paradigm described below. The operative word is "seems". In fact, the array function behaves correctly. The root cause of the problem is an anomaly (defect?) of the paradigm implemented in the testit() macro below. The root cause of the problem seems to be related to setting Application.Calculation=xlCalculationManual, executing Selection.Calculate, then restoring Application.Calculation=xlCalculationAutomatic. The problem manifests itself in the last step, setting Application.Calculation=xlCalculationAutomatic. This paradigm is common when measuring performance. For example, see http://msdn.microsoft.com/en-us/library/aa730921.aspx, Sub RangeTimer() in the section "Measuring Calculation Time". I have instrumented the testit() macro and myInts() function to make the root cause more apparent. As the instrumentation demonstrates, the problem is: when we set Application.Calculation=xlCalculationAutomatic after Selection.Calculate performed a recalculation, myInts() is invoked again from each of the cells in A1:A10 individually, not as an array. Thus, Application.Caller is a single cell, not a range; and Application.Caller.Rows.Count and Application.Caller.Columns.Count are 1 instead of the reflecting the shape of the array formula, as myInts() expects. Consequently, the array formula cannot return a set of unique integers. Instead, it always returns the first integer in the set. (But Selection.Calculate did invoke myInts() once with the array formula range, and the function did return a set of unique integers. This is evident by setting a breakpoint after Selection.Calculate.) Similarly, the RandInt() functions in an array formula will not necessarily return a set of unique integers when they are executed by the testit() macro. However, LINEST does not have such a problem when Selection is the array formula described in the footnote below. After testit() is executed, V1:W5 will contain their expected values[*], not the value of V1 repeatedly as we might expect given the analysis above. So I suspect that LINEST is using something other than (just) Application.Caller to determine the shape of the array formula that includes Application.Caller, and it is returning the result in the array that corresponds to the position of Application.Caller in the array formula. How might that be done? Alternatively, is there a way to implement the paradigm in the macro testit() so as to avoid the anomaly when Application.Calculation=xlCalculationAutomatic is restored? ----- Endnotes Private callcnt As Long Sub testit() Dim oldIter, oldCalc Debug.Print "=====" callcnt = 0 With Application oldIter = .Iteration oldCalc = .Calculation .Iteration = False .Calculation = xlCalculationManual On Error GoTo done Selection.Calculate done: .Iteration = oldIter .Calculation = oldCalc MsgBox "callcnt = " & callcnt End With End Sub Function myInts() Dim rows As Long, cols As Long, n As Long, x() As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myInts: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols ReDim x(1 To rows, 1 To cols) n = 1 For r = 1 To rows: For c = 1 To cols x(r, c) = n: n = n + 1 Next c: Next r myInts = x End With End Function Function myZero() Dim rows As Long, cols As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myZero: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols End With End Function [*] Actually, even LINEST might behave slight differently. Consider the following simplistic example. X1:X4 contains 1 through 4, Y1 contains =2*X1+5, which is copied down through Y4, and V1:W5 contains the array formula =LINEST(Y1:Y4,X1:X4,TRUE,TRUE)+myzero(). When executed normally, V4 results in #NUM!. I assume that is correct under the circumstances. But when executed using the testit() macro, V4 results in zero. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoidwrong results?
Hello,
I do not get your results (using Excel 2007 right now). BTW: I suggest to use OPTION EXPLICIT and not to use Excel key words ("rows") as variable names. It might not change your test result but ... Regards, Bernd |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
"Bernd P" wrote:
I do not get your results (using Excel 2007 right now). Good to know. I thought it was a defect. But there still must be an alternative to (or property of) Application.Caller that allowed the Excel 2003 LINEST array formula to work correctly in this paragidm (the testit macro). That is, some way that LINEST could determine the correct shape and size of the array formula range, even when it is called from an individual cell within the array formula range. I am looking for the method that LINEST might have used. Apparently it is not Application.Caller.Rows and Application.Caller.Columns. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
I do not think the problem is with Application.Caller : looks more like a
problem with the dependency tree in the calculation engine. I can duplicate your problem with Excel 2003 , but in your test case its caused by the fact that you have not flagged the myInts() function as volatile even though it has no parameter arguments and hence no external dependencies. I suspect this is a variation on the already mentioned behaviour noted at http://www.decisionmodels.com/calcsecretsj.htm add Application.Volatile to myInts() and the problem goes away for me. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Joe User" <joeu2004 wrote in message ... "Bernd P" wrote: I do not get your results (using Excel 2007 right now). Good to know. I thought it was a defect. But there still must be an alternative to (or property of) Application.Caller that allowed the Excel 2003 LINEST array formula to work correctly in this paragidm (the testit macro). That is, some way that LINEST could determine the correct shape and size of the array formula range, even when it is called from an individual cell within the array formula range. I am looking for the method that LINEST might have used. Apparently it is not Application.Caller.Rows and Application.Caller.Columns. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
"Charles Williams" wrote:
I do not think the problem is with Application.Caller : looks more like a problem with the dependency tree in the calculation engine. Certainly! There was never any doubt about that. The point is: the "problem with the calculation engine" has a side-effect for certain uses of Application.Caller, for example as it used in the RandInt and myInts functions. The side-effect causes such functions to misbehave in a specific context. add Application.Volatile to myInts() and the problem goes away for me. Interesting! So you think that LINEST is a volatile function?(!) I don't. First, it is not in the list of volatile functions at http://www.decisionmodels.com/calcsecretsi.htm. Second, the myZero() instrumentation demonstrates that the LINEST array formula is __not__ recalculated every time any cell in the workbook is modified, a nasty side-effect of making a UDF volatile. And I don't think adding Application.Volatile to a UDF needlessly is a good work-around, for the very reason that it has that nasty side-effect. That echos the advice at http://www.decisionmodels.com/calcsecretsi.htm, to wit: "Avoid volatile functions wherever possible." Again, I am looking for an explanation of how LINEST might work properly in this context, whereas the UDFs do not as they are currently designed. I am about to give up on this. I have explored the properties and methods of the Application.Caller object as best I can, to no avail. When the array UDF is called "unexpectedly" (shorthand for: invoked from each cell in an array formula range individually), Application.Caller.HasArray is true, as expected. But I cannot find a way to make use of that information to identify the full array formula range that includes the individual cell. A.C.Address reflects the single cell, as do A.C.Rows, A.C.Cols and A.C.CurrentArray. (The last one surprises me.) I also tried A.C.SpecialCells(xlCellTypeFormulas) and A.C.Parent, to no avail. (No surprise there; just fishing.) And maybe that's the question I should be asking.... When a UDF is called from a cell that contains an array formula (never mind how), how can the UDF determine the full array formula range that includes that cell? LINEST does it, apparently. ----- original message ----- "Charles Williams" wrote in message ... I do not think the problem is with Application.Caller : looks more like a problem with the dependency tree in the calculation engine. I can duplicate your problem with Excel 2003 , but in your test case its caused by the fact that you have not flagged the myInts() function as volatile even though it has no parameter arguments and hence no external dependencies. I suspect this is a variation on the already mentioned behaviour noted at http://www.decisionmodels.com/calcsecretsj.htm add Application.Volatile to myInts() and the problem goes away for me. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Joe User" <joeu2004 wrote in message ... "Bernd P" wrote: I do not get your results (using Excel 2007 right now). Good to know. I thought it was a defect. But there still must be an alternative to (or property of) Application.Caller that allowed the Excel 2003 LINEST array formula to work correctly in this paragidm (the testit macro). That is, some way that LINEST could determine the correct shape and size of the array formula range, even when it is called from an individual cell within the array formula range. I am looking for the method that LINEST might have used. Apparently it is not Application.Caller.Rows and Application.Caller.Columns. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
"Charles Williams" wrote:
In any case LINEST is an XLL (C/C++ or XLM), its not a VBA function so does not use Application.Caller which is a VBA thing. I presumed that to be the case. I was speaking metaphorically. Translation: what is there in VBA-speak that would allow an array UDF to behave like LINEST in this context (the testit() paradigm)? If you are saying, "nothing that I know of", that's fine. It's really okay to say "I don't know" sometimes. I assume that the implementation of LINEST relies on the same data structures and interfaces that VBA objects are based on. But that does not mean that VBA provides access to all data structures and interfaces that are available. This might be one of those cases. Your myInt() function does not follow Excel's rules about dependencies/parameters, so needs to be declared as volatile ..... But only to work around this defect. Right? Is there any other situation where the non-volatile myInt function would misbehave? I see nothing about myInt() that requires volatility. It can be executed one time, namely when the array function is entered or edited. Likewise with Bernd's RandInt(), if the intent is to avoid incessantly regenerating the set of random integer with each and every cell modification in the workbook, which is insane behavior IMHO. I hadn't noticed that McGimpsey's RandInt() is volatile. I think a reasonable person would comment out that line to avoid the inane behavior, unless it is desired of course(!). I don't know what "Excel rules" you are alluding to regarding dependencies/parameters and Application.Volatile. The VBA help page says: "A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change". Neither statement implies that we __must__ have Application.Volatile if there are no function parameters, unless of course we want the UDF to be recalculated incessantly. My intent is just the opposite. I assume that is Bernd's intent as well. But just to placate you, I added a variant "arg" to the non-volatile myInt(). I call it as =myInt(X1), array-entered in A1:A10, where X1 contains a constant. That did not change the misbehavior in the "unexpected" situation (as I defined "unexpected" previously). (No surprise.) I even added a statement inside myInt() of the form "n = arg". Again, that did not change the misbehavior. (No surprise.) What "Excel rules" about dependencies/parameters and Application.Volatile does that not follow now? ;-) ----- original message ----- "Charles Williams" wrote in message ... Joe, Read my post again please Interesting! So you think that LINEST is a volatile function?(!) Thats not what I said: LINEST is not a volatile function, and does not need to be because all of its inputs are defined as parameters. Your myInt() function does not follow Excel's rules about dependencies/parameters, so needs to be declared as volatile, LINEST does follow the rules so does not. Again, I am looking for an explanation of how LINEST might work properly in this context, whereas the UDFs do not as they are currently designed. It works because it follows the rules, your function does not, and so your myInt() function encounters some nasty consequences of not following the rules. In any case LINEST is an XLL (C/C++ or XLM), its not a VBA function so does not use Application.Caller which is a VBA thing. Application.Caller is correctly telling you the circumstances in which your function is being executed: so focus on fixing the circumstances rather than trying to change the messenger. But there is definitely a bug lurking there to catch the unwary: the good news is that it does not look fatal. regards Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
Looks like MSoft introduced the problem when they added within selection
dependency checking to Range Calculate in Excel 2002. Seems to be fixed in Excel 2007. When you add If Val(Application.Version) 9 And Val(Application.Version) < 12 Then Selection.Dirty End If immediately after the Selection.Calculate: it seems to work OK in all versions Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Charles Williams" wrote in message ... I do not think the problem is with Application.Caller : looks more like a problem with the dependency tree in the calculation engine. I can duplicate your problem with Excel 2003 , but in your test case its caused by the fact that you have not flagged the myInts() function as volatile even though it has no parameter arguments and hence no external dependencies. I suspect this is a variation on the already mentioned behaviour noted at http://www.decisionmodels.com/calcsecretsj.htm add Application.Volatile to myInts() and the problem goes away for me. Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "Joe User" <joeu2004 wrote in message ... "Bernd P" wrote: I do not get your results (using Excel 2007 right now). Good to know. I thought it was a defect. But there still must be an alternative to (or property of) Application.Caller that allowed the Excel 2003 LINEST array formula to work correctly in this paragidm (the testit macro). That is, some way that LINEST could determine the correct shape and size of the array formula range, even when it is called from an individual cell within the array formula range. I am looking for the method that LINEST might have used. Apparently it is not Application.Caller.Rows and Application.Caller.Columns. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Alternative to Application.Caller in array functions to avoid wrong results?
Thanks for the constructive response.
----- original message ----- "Charles Williams" wrote in message ... Looks like MSoft introduced the problem when they added within selection dependency checking to Range Calculate in Excel 2002. Seems to be fixed in Excel 2007. When you add If Val(Application.Version) 9 And Val(Application.Version) < 12 Then Selection.Dirty End If immediately after the Selection.Calculate: it seems to work OK in all versions Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com ----- original posting ----- "Joe User" <joeu2004 wrote in message ... "Bernd P" wrote in Newsgroups: microsoft.public.excel.worksheet.functions Subject: how can I use randbetween without repeating numbers in a set Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT) Message-ID: See JE McGimpsey's site for a solution to this problem. http://www.mcgimpsey.com/excel/udfs/randint.html A faster and more flexible RandInt you can find he http://sulprobil.com/html/randint.html These array functions use Application.Caller to determine the size and shape of the array result to be returned. This poses a problem when they are executed from a macro using Selection.Calculate under specific conditions, explained below. At least, that is the case with my revision of Excel 2003 (11.5612.5606) and VBA 6.3 (9969, 6.4.8869), part of MS Office 2003 Sm Busn Ed. However, I do not observe a similar problem with some Excel array functions, e.g. LINEST[*]. So I am wondering: is there an alternative to Application.Caller that will work under the specific conditions that exhibit the problem? The problem might not always be apparent with Bernd's and McGimpsey's RandInt() functions due to their random nature. So for demonstration purposes, consider the array function myInts() below, which returns an array of unique integers in a deterministic manner. When it is invoked by an array formula in A1:B5, e.g. =myInts(), it returns the integers 1 through 10. However, the array formula __seems__ to misbehave, returning 1 in all 10 cells, when it is invoked by a macro that implements the paradigm described below. The operative word is "seems". In fact, the array function behaves correctly. The root cause of the problem is an anomaly (defect?) of the paradigm implemented in the testit() macro below. The root cause of the problem seems to be related to setting Application.Calculation=xlCalculationManual, executing Selection.Calculate, then restoring Application.Calculation=xlCalculationAutomatic. The problem manifests itself in the last step, setting Application.Calculation=xlCalculationAutomatic. This paradigm is common when measuring performance. For example, see http://msdn.microsoft.com/en-us/library/aa730921.aspx, Sub RangeTimer() in the section "Measuring Calculation Time". I have instrumented the testit() macro and myInts() function to make the root cause more apparent. As the instrumentation demonstrates, the problem is: when we set Application.Calculation=xlCalculationAutomatic after Selection.Calculate performed a recalculation, myInts() is invoked again from each of the cells in A1:A10 individually, not as an array. Thus, Application.Caller is a single cell, not a range; and Application.Caller.Rows.Count and Application.Caller.Columns.Count are 1 instead of the reflecting the shape of the array formula, as myInts() expects. Consequently, the array formula cannot return a set of unique integers. Instead, it always returns the first integer in the set. (But Selection.Calculate did invoke myInts() once with the array formula range, and the function did return a set of unique integers. This is evident by setting a breakpoint after Selection.Calculate.) Similarly, the RandInt() functions in an array formula will not necessarily return a set of unique integers when they are executed by the testit() macro. However, LINEST does not have such a problem when Selection is the array formula described in the footnote below. After testit() is executed, V1:W5 will contain their expected values[*], not the value of V1 repeatedly as we might expect given the analysis above. So I suspect that LINEST is using something other than (just) Application.Caller to determine the shape of the array formula that includes Application.Caller, and it is returning the result in the array that corresponds to the position of Application.Caller in the array formula. How might that be done? Alternatively, is there a way to implement the paradigm in the macro testit() so as to avoid the anomaly when Application.Calculation=xlCalculationAutomatic is restored? ----- Endnotes Private callcnt As Long Sub testit() Dim oldIter, oldCalc Debug.Print "=====" callcnt = 0 With Application oldIter = .Iteration oldCalc = .Calculation .Iteration = False .Calculation = xlCalculationManual On Error GoTo done Selection.Calculate done: .Iteration = oldIter .Calculation = oldCalc MsgBox "callcnt = " & callcnt End With End Sub Function myInts() Dim rows As Long, cols As Long, n As Long, x() As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myInts: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols ReDim x(1 To rows, 1 To cols) n = 1 For r = 1 To rows: For c = 1 To cols x(r, c) = n: n = n + 1 Next c: Next r myInts = x End With End Function Function myZero() Dim rows As Long, cols As Long callcnt = callcnt + 1 With Application.Caller rows = .rows.Count cols = .Columns.Count Debug.Print "myZero: callcnt="; callcnt; ", "; _ .Address; ", rows="; rows; ", cols="; cols End With End Function [*] Actually, even LINEST might behave slight differently. Consider the following simplistic example. X1:X4 contains 1 through 4, Y1 contains =2*X1+5, which is copied down through Y4, and V1:W5 contains the array formula =LINEST(Y1:Y4,X1:X4,TRUE,TRUE)+myzero(). When executed normally, V4 results in #NUM!. I assume that is correct under the circumstances. But when executed using the testit() macro, V4 results in zero. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Is Application.Caller valid in Functions but not Subs? | Excel Programming | |||
Application.Caller | Excel Discussion (Misc queries) | |||
Array formula returning wrong results | Excel Discussion (Misc queries) | |||
Row = Application.Caller.Row | Excel Worksheet Functions | |||
Application.Caller | Excel Programming |