Alternative to Application.Caller in array functions to avoid wrong results?

[Joe User]

Newsgroups: microsoft.public.excel.worksheet.functions
Subject: Re: how can I use randbetween without repeating numbers in a set
Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT)
Message-ID:

A faster and more flexible RandInt you can find here:
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.

 

[joel]

I have found that I can get array formulas to return more than on
location in excel 2003 from a UDF if I know the size of the array to b
returned. I simply enter the formula in the first cells, copy th
formual to the destijnation cells, then select all the cells where th
results are to be return and enter Shft-Cntl-enter

 

[Charles Williams]

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

Newsgroups: microsoft.public.excel.worksheet.functions
Subject: Re: how can I use randbetween without repeating numbers in a set
Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT)
Message-ID:

A faster and more flexible RandInt you can find here:
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.

 

[Joe User]

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

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

Newsgroups: microsoft.public.excel.worksheet.functions
Subject: Re: 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 here:
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.

 

[Bernd P]

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

 

[Joe User]

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.

 

[Charles Williams]

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]

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]

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

 

[Joe User]

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]

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

 

[Joe User]

Thanks for the constructive response.


----- original 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 -----

Newsgroups: microsoft.public.excel.worksheet.functions
Subject: Re: how can I use randbetween without repeating numbers in a set
Date: Thu, 29 Oct 2009 04:31:49 -0700 (PDT)
Message-ID:

A faster and more flexible RandInt you can find here:
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.