recalculating RAND() inside a VBA loop

[vbaRay]

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

 

[Jim Cone]

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

 

[Dave Peterson]

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

 

[Dave Peterson]

And by fails, I mean that the code continues, but the cell's value doesn't
change.

 

[Jim Cone]

Hi Dave,
I called the function from a sub.
The ActiveCell had "=RAND()" in it...
'---
Sub huh()
MsgBox expval(ActiveCell, 5)
End Sub
'---
Jim Cone
Portland, Oregon USA


"Dave Peterson"
wrote in message
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
Dave Peterson

 

[Dave Peterson]

Calling the function from another function or sub wouldn't be a problem.

It'll be a problem when the function originates in a cell on a worksheet.

We'll see (maybe) who guessed right <vbg>.