It worked for me in xl2002. However I had to correct the array dimension
so the average was correct... ReDim arr(1 To loops)

ReDim arr(loops) is the same as ReDim arr(0 To loops)
--
Jim Cone
Portland, Oregon USA





"vbaRay"
wrote in message...
How do I get cells in spreadsheet to recalculate new values using RAND() when
looping inside VBA? I'm trying to compute the average value of a cell who's
dependants contain values generated from the RAND() function.
Sample code is below.
Many thanks for any insights.

Function expval(target As Range, Optional iter As Integer) As Double
'measure value
'record in an array
'recompute value
'repeat
'compute average value

Dim rowLocation As Integer
Dim colLocation As Integer
Dim arr() As Double
Dim loops As Integer
Dim X As Integer

'determine cell location
rowLocation = target.Row
colLocation = target.Column

'set itterations
loops = Application.WorksheetFunction.Max(iter, 20)
'set array
ReDim arr(loops)

'loop through value measurements
For X = 1 To loops
Calculate 'for some reason, the cell is not updating it value when using
RAND() on the spreadsheet
arr(X) = Cells(rowLocation, colLocation).Value
Next X

'compute average value
expval = Application.WorksheetFunction.Average(arr)
End Function