About the only thing that a function called from a worksheet cell can do is
return a value to the cell with the function.

Application.calculate fails for me (xl2003).

Maybe you could use VBA's Rnd function instead of looking back at the
worksheet???

Option Explicit
Function expval(Optional iter As Long) As Double

Dim arr() As Double
Dim loops As Integer
Dim X As Long

Randomize

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

'set array
ReDim arr(1 To loops)

'loop through value measurements
For X = 1 To loops
arr(X) = Rnd
Next X

'compute average value

expval = Application.WorksheetFunction.Average(arr)

End Function

vbaRay wrote:

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

--

Dave Peterson