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.

--
Dana DeLouis


"Randy Harmelink" wrote in message
oups.com...
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.