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

10x faster (at least in my simplified test file) after implementing array solution in the inner loop.

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 Integer
    Dim j As Byte
    Dim ar As Variant

        Set wksData = Sheets("Data")
        wksData.Range("O3:P1048576").ClearContents
        StartTime = Timer
            With wksData
                For x = 3 To 1002
                    ReDim ar(1 To 159, 1 To 1)
                    For j = 1 To UBound(ar, 1)
                        ar(j, 1) = Rnd()
                    Next
                    .Range("O3").Resize(UBound(ar, 1)).Value = ar
                    wksData.Calculate
                    Erase ar
                    .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

They key idea why you would want to implement arrays, is to reduce the count of transitions between VBE and the worksheet. In your previous code you had to go to the worksheet to write data to cells in every iteration. Now you do Rnd() for values in the array which is not stored in the worksheet until it is completed. Once it's done you go to the worksheet and output the result!

Other changes are very minor. I replaced Doubles with Integer and Byte, as they require less memory. Also, in For ... Next I got rid of repeated variable after Next. No point in doing that, the compiler knows which loop it is in.