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