I think your theory is correct. The function gets called as many times as I
have in the selected cells (application.caller.count).

I used this to count.

Option Explicit
Function Tester(pParm As Variant)
Static ctr As Long
Dim vData(1 To 2, 1 To 3) As Variant
Dim i1 As Integer, i2 As Integer
ctr = ctr + 1
For i1 = 1 To 2
For i2 = 1 To 3
vData(i1, i2) = i1 & "," & i2
Next i2: Next i1
vData(1, 1) = Application.Caller.Count & " Cells"
vData(1, 2) = ctr
Debug.Print ctr
Tester = vData
End Function

Run|Reset will reset that ctr variable.

=======
Maybe you could make it volatile in a different way:

=tester()&TEXT((0*RAND()),"")
for text
(I didn't pass it anything)
or
=tester()+(0*RAND())
for a number

Each still an array formula.

If you're returning text and numbers, then this won't work.


Randy Harmelink wrote:

I have a function that started failing when I passed RAND() as a
parameter and was hoping someone could tell me why. Here's a
simplified version of the UDF -- an array-entered function to return a
2-row by 3-column array:

Function Tester(pParm As Variant)
Dim vData(1 To 2, 1 To 3) As Variant
Dim i1 As Integer, i2 As Integer
For i1 = 1 To 2
For i2 = 1 To 3
vData(i1, i2) = i1 & "," & i2
Next i2: Next i1
vData(1, 1) = Application.Caller.Count & " Cells"
Tester = vData
End Function

If I array-enter "=Tester(1)" over a 2-row by 3-column range, I get "6
Cells" in the first cell of the range. But if I array-enter
"=Tester(RAND())" over a 2-row by 3-column range, I get "1 Cell" in
the first cell of the range. Any idea why using RAND() as a parameter
changes the value of Application.Caller.Count? It also seems to cause
a circular reference error in my spreadsheet. When I go into DEBUG
mode on the second call, Application.Caller appears to be a number
instead of an object.

I think the reason Application.Caller.Count AND the circular reference
problems are occurring is because RAND() is forcing the the UDF to
evaluate (or whatever) once for each cell of the range -- the function
seems to be getting executed six times. But I can't imagine why it
would work that way.

I'm using this technique to attempt to make a function "volatile" on
demand -- since RAND() would change in value any time F9 is pressed,
which would trigger the function to recalculate. For now, it seems to
be working fine if I use the NOW() function instead of RAND(), but the
effect of RAND() has me concerned.

Any ideas? TIA.

--

Dave Peterson