Excel VBA get cell value after recalculation

Question

I have a worksheet that calculates various values based on a random value and would like to use the law of large numbers to converge to an average for each calculation.

I am thinking of using VBA to execute the calculation 1000's of times and store the values in a list for averaging at the end. My current testing code only stores the original value after each iteration. ie Safety1 does not change even though the value in R36 changes.

Dim Safety1(0 To 10) As Long


For i = 0 To 10

    Safety1(i) = Sheet34.Range("R36").Value
    Debug.Print Safety1(i)

Next i

myAverage = Application.WorksheetFunction.Average(Safety1)

myAverage should be the converging average.

R36 contains the sum of other ranges, which contain values based on rand()

If there is a better way to do this, i am happy to listen.

Thanks in advance.

That loop is going to loop the same cell data over and over again before the sheet recalcs. You need to look into a worksheet_change event that calls a routine with a static or global variable that won't be erased between iterations. — K.Dᴀᴠɪs, Feb 04 '20 at 00:41

score 0 · Accepted Answer · answered Feb 04 '20 at 00:54

0

This post resolved the problem. I needed to wait until the calculation process had completed before storing the value

answered Feb 04 '20 at 00:54

theotheraussie

495
1
4
14

score 0 · Answer 2 · answered Feb 04 '20 at 01:36

Please do sheet calculate like this:

Dim Safety1(0 To 10) As Long

Application.ScreenUpdating = False    

For i = 0 To 10
    Worksheets("Sheet34").Calculate
    Safety1(i) = Sheet34.Range("R36").Value
    Debug.Print Safety1(i)

Next i
Application.ScreenUpdating = True
myAverage = Application.WorksheetFunction.Average(Safety1)

Excel VBA get cell value after recalculation

2 Answers2