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