Double Cycle Performance Optimization in VBA Excel

Question

I am interested in learning the fastest way to execute a set of instructions in a double loop for cyclic conversion to a two-dimensional range of cells. My code would be like this:

Sub Test()

For i = 1 To 1000000
    For j = 1 To 10   'It can be more than 10
        'I put a set of instructions here
    Next j
Next i

End Sub

For example, suppose I write simple code to accomplish the following task:

Sub Test1()
T0 = Timer
For i = 1 To 1000000
    For j = 1 To 10
        Cells(i, j) = j + Rnd()
    Next j
Next i
InputBox "The runtime of this program is ", "Runtime", Timer - T0
End Sub

Test1 , 179.6406 . (i j), Test1 , Variant. Test1, Longs, VBA Longs. Test2 , 168.7539 ( 11 ).

Test2, Excel, , Test2.

Sub Test3()
Dim i As Long, j As Long
T0 = Timer

ScreenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
CalcState = Application.Calculation
EventsState = Application.EnableEvents
DisplayPageBreakState = ActiveSheet.DisplayPageBreaks
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

For i = 1 To 1000000
    For j = 1 To 10
        Cells(i, j) = j + Rnd()
    Next j
Next i

Application.ScreenUpdating = ScreenUpdateState
Application.DisplayStatusBar = StatusBarState
Application.Calculation = CalcState
Application.EnableEvents = EventsState
ActiveSheet.DisplayPageBreaks = DisplayPageBreaksState

InputBox "The runtime of this program is ", "Runtime", Timer - T0
End Sub

Test2 Test3 96.13672 . , . - ? , .

Answer 1

VBA, 10 :

Sub QuickTest()
    Dim v(1 To 1000000, 1 To 10) As Double
    For i = 1 To 1000000
        For j = 1 To 10
            v(i, j) = j + Rnd
        Next j
    Next i

    Range("A1:J1000000") = v
End Sub

:

• VBA ""
• , .

EDIT # 1:

:

Sub QuickTest2_The_Sequel()
    Dim i As Long, j As Long, m As Long, n As Long
    Range("K1") = Evaluate("Now()")

    m = 10
    n = 1000000

    ReDim v(1 To n, 1 To m) As Double
    For i = 1 To n
        For j = 1 To m
            v(i, j) = j + Rnd
        Next j
    Next i

    Range("A1:J" & n) = v
    Range("K2") = Evaluate("Now()")
End Sub

K1 K2 . ReDim Dim "" :

Answer 2

Sub Checkthis()
    starttime = Format(Now(), "hh:mm:ss")
    Dim i, j As Long
    Dim a(1000000, 10) As Long
    For i = 1 To 1000000
        For j = 1 To 10
            a(i, j) = j + Rnd
        Next j
    Next i
    Range("A1:J1000000") = a
    endtime = Format(Now(), "hh:mm:ss")
    Elapsed = DateDiff("s", starttime, endtime)
    MsgBox ("Finished in " & Elapsed & " seconds")
End Sub

Answer 3

, E, . "E" .

Sub Test3()

Dim i As Long, j As Long, LastRow As Long
T0 = Timer

' in case Column E allways has data for each row >> you can modify to your needed Column
LastRow = Cells(Rows.count, "E").End(xlUp).row

ScreenUpdateState = Application.ScreenUpdating
StatusBarState = Application.DisplayStatusBar
CalcState = Application.Calculation
EventsState = Application.EnableEvents
DisplayPageBreakState = ActiveSheet.DisplayPageBreaks
Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False

For i = 1 To LastRow
    For j = 1 To 10
        Cells(i, j) = j + Rnd()
    Next j
Next i

Application.ScreenUpdating = ScreenUpdateState
Application.DisplayStatusBar = StatusBarState
Application.Calculation = CalcState
Application.EnableEvents = EventsState
ActiveSheet.DisplayPageBreaks = DisplayPageBreaksState

InputBox "The runtime of this program is ", "Runtime", Timer - T0

End Sub