UDF -- Application.Caller problem with RAND()?

[Randy Harmelink]

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.

 

[Guest]

I'm very interested to learn why this is so. I have tried to replicate your
example and sure enough I see exactly what you are talking about. However,
my results are a bit different. Every time I try this, the UDF recalcs for
as many items in the array, but each time the application.caller is one
cell, never a number as you experience.

While I can't explain why RAND is doing this, one way around the problem is
to create another UDF that calculates a random number and use that in the
main UDF instead of the parameter RAND. That will fix the Application.Caller
problem.

 

[Dave Peterson]

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.

 

[Dana DeLouis]

Hi. If your code was just this...

Function Tester(pParm)
Debug.Print Application.Caller.Address
Tester = 1
End Function

The immediate window would show that it was called once for each cell.
I think RAND() is one of the rare functions that takes no arguments, and
therefore behaves a little differently. For example, I've never gotton
Rand() to fill an array with random numbers without resorting to looping.