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

Thanks for all of your responses. Below is the code I ultimately settled on. It ran in 284.61 seconds or just over 4.5 minutes for 1,000,000 random scenarios (down from the original 48 minutes). I tested the array solution which worked rather quickly on a small number of random scenarios, but was actually slower when testing for 1,000,000 (not sure why).

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 Long

    Set wksData = Sheets("Data")
    wksData.Range("N3:O1048576").ClearContents
    StartTime = Timer

        With wksData
            For x = 3 To 1000002
                .Range("N3:N161").Formula = "=Rand()"
                .Range("M1:M161").Calculate
                .Range("O" & x) = .Range("M1")
            Next
            wksData.Range("N3:N161").Copy
            wksData.Range("N3:N161").PasteSpecial xlPasteValues
            wksData.Calculate
        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

Answer 2

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

Answer 3

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.

Answer 4

remove this line:

           wksData.Calculate

You've gone to the trouble of setting Calculation to manual, then you recalculate on every loop anyway.

Alternate option:

in your worksheet, set P3 =$N$1, then copy P3 through to P1002.

In your code, remove the For x = 3 to 1002 loop.