Re: recalculating RAND() inside a VBA loop

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

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 work***.

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

Jim Cone wrote:

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 work*** 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
work***???

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
.

Quantcast