Improving the speed of a nested loop where random numbers are generated

Question

I am trying to improve the speed of my code.

I added a timer to determine how long it takes for my code to run. It takes about 4.14 for every 1,000 iterations.

I've read some posts regarding writing to an array and reading it back, but how to apply that idea here? Perhaps, there is another method as well.

Sub Random()

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = False
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    ActiveSheet.DisplayPageBreaks = False

    Dim wksData As Worksheet
    Dim StartTime As Double
    Dim SecondsElapsed As Double
    Dim x As Double
    Dim i As Double    

    Set wksData = Sheets("Data")
    wksData.Range("O3:P1048576").ClearContents             
    StartTime = Timer
    With wksData
        For x = 3 To 1002
            For j = 3 To 161
                .Range("O" & j) = Rnd()  
            Next j
            wksData.Calculate
            .Range("P" & x) = .Range("N1")
        Next x
    End With
    SecondsElapsed = Round(Timer - StartTime, 2)
    MsgBox "Macro ran successfully in " & SecondsElapsed & " seconds", vbInformation

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
    Application.DisplayStatusBar = True
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    ActiveSheet.DisplayPageBreaks = True

End Sub

N1 sums the costs which come from formulas on the worksheet. I am taking the cost each time a series of random numbers are generated.

Answer 1

You could use a volatile function and get rid of the inner loop altogether, that takes you down from 157,842 iterations to 999 for a start:

With wksData
    .Range("O3:O161").Formula = "=RAND()"
    For x = 3 To 1002 
        .Calculate
        .Range("P" & x) = .Range("N1")
    Next x
End With