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.