Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
recalculating RAND() inside a VBA loop
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
use time functions inside if loop | Excel Worksheet Functions | |||
Can an array be redim inside a for loop? | Excel Programming | |||
form inside a loop | Excel Programming | |||
Increment stopvalue inside a For loop | Excel Programming | |||
Create an array inside a For...Next loop | Excel Programming |