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 |
recalculating RAND() inside a VBA loop
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 |
recalculating RAND() inside a VBA loop
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 |
recalculating RAND() inside a VBA loop
And by fails, I mean that the code continues, but the cell's value doesn't
change. Dave Peterson wrote: 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 -- Dave Peterson |
recalculating RAND() inside a VBA loop
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 |
recalculating RAND() inside a VBA loop
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. 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 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 |
All times are GMT +1. The time now is 01:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com