Optimizing performance of double loop in VBA Excel

Question

I'm interested in knowing the fastest way to execute a set of instructions in double loop to loop through a two-dimensional range of cells. My code will 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 a simple code to implement such a 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

I ran the procedure Test1 on my machine, it took 179.6406 seconds to complete. Since I don't declare the variables (i and j), the Test1 is running with those variables defaulting to the Variant type. I then add one line to the Test1 to declare the variables as Longs, since VBA is optimized for Longs. The new procedure, Test2, brought the running time on my machine down to 168.7539 seconds (almost 11 seconds faster).

To improve performance of Test2, I turned off Excel functionality that isn't needed while Test2 code runs.

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

The above method helps improve the performance of Test2 and Test3 completes in 96.13672 seconds on my machine. So I'm wondering if there is a more effective way for doing this. Can anyone come up with a quicker version? If possible, even avoiding the double loop procedure.

is it must be 1,000,000 times ? or the range can be dynamic and you can search the last row ? — Shai Rado
– Shai Rado, Commented Jul 20, 2016 at 11:56
I think you could try a "For Each Cell" loop across the square range, although I have no idea if that would be any faster (I kind of suspect it wouldnt be) — RGA
– RGA, Commented Jul 20, 2016 at 11:56
Just as an aside, minus a typo or two, nearly perfect grammar/English in this post :) plus, a very good question — RGA
– RGA, Commented Jul 20, 2016 at 12:13
@RGA Feel free to edit it. I want to make SO not only is a site to improve my coding skills, but also to make my English gets better and better. :) — Anastasiya-Romanova 秀
– Anastasiya-Romanova 秀, Commented Jul 20, 2016 at 12:17

Gary's Student · Accepted Answer · 2016-07-20 13:15:24Z

7

I used an internal VBA array and this ran in under 10 seconds:

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

Note:

the use of an internal VBA array allows us to avoid "touching" the worksheet more than once
the array can be transferred to the worksheet cells in one step rather than in a loop.

EDIT#1:

Consider this sequel:

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

Here we use cells K1 and K2 to record the start and stop times. We also use ReDim rather than Dim to "paramatize" the limits:

edited Jul 20, 2016 at 13:15

answered Jul 20, 2016 at 12:04

Gary's Student

96.9k10 gold badges66 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

RGA Over a year ago

Worth mentioning in the answer, it is ALWAYS faster (if the option is available to you) to write values to an array and then write the array values to the cells, rather than perpetually accessing the cells

Anastasiya-Romanova 秀 Over a year ago

FWIW, Dim v(1 To 1000000, 1 To 10) As Variant makes it run faster. It completes in 5.546875 s! (+1)

RGA Over a year ago

@Anastasiya-Romanova秀 as far as I'm aware, this is the fastest way to loop through a data set. It saves time by only accessing the Cells once, rather than accessing it for each cell individually. Very worthy of being an accepted answer IMO

Anastasiya-Romanova 秀 Over a year ago

@RGA This is indeed a great answer, at least for me. But I wanna wait for some time in case there is someone here who comes up with a better way than this.

Anastasiya-Romanova 秀 Over a year ago

Sir, how if the rows and columns are dynamic inputs? I try n = 1000000 and m = 10, but Dim v(1 To n, 1 To m) As Long returns error.

|

Karthick Gunasekaran · Accepted Answer · 2016-07-20 13:17:35Z

1

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

answered Jul 20, 2016 at 13:17

Karthick Gunasekaran

2,7231 gold badge17 silver badges26 bronze badges

2 Comments

Anastasiya-Romanova 秀 Over a year ago

Thanks for the answer. (+1)

Anastasiya-Romanova 秀 Over a year ago

There is an issue using your code. I Think we must declare the length of the array more specific like Gary's. If not, then the 1st row and 1st column of Range("A1:J1000000") will be equal to 0.

Shai Rado · Accepted Answer · 2016-07-20 12:03:51Z

0

Try the following code, it checks the last row in Column E, and the Loop stops there. You can modify the "E" to whatever Column you need.

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

answered Jul 20, 2016 at 12:03

Shai Rado

33.7k7 gold badges32 silver badges55 bronze badges

8 Comments

RGA Over a year ago

dont think this is really answering OP's question... her issue wasn't about changing the loop size, but making a loop run faster for a given size

Shai Rado Over a year ago

@RGA again ? what is your problem ? look at her question, and her answers below, her range can by dynamic. Maybe she is putting 1000000 just to be sure ? maybe its 1000 rows. so having the loop scan only 1/1000 will not shorten her time ? really ?

RGA Over a year ago

OP's question is asking about optimizing a loop to run faster. It's a theoretical question (perhaps not technically within the scope of SO but that is a different issue). Yes, shrinking the size of a loop will obviously make it run faster, but OP's question is about improving the speed of a loop for a given size.

RGA Over a year ago

I don't have any problem with you specifically, as I will happily point out issues with answers with anyone on SO who isn't answering correctly, so perhaps the fact that I am commenting often on your posts is an indication that you should review properly answering questions. In this particular case, this is technically an answer, just not an answer to what OP wanted. Note also the "+ 1" on my original comment, which indicates that someone else agrees with my assessment

RGA Over a year ago

It's nothing personal at all, I just want SO to be the best it can be (as I'm sure you do as well, given your commendable and active involvment). Furthermore, I welcome anyone to make the same types of comments on my posts so that I can either delete or correct them to ensure that the highest quality of information is provided

|

Collectives™ on Stack Overflow

Optimizing performance of double loop in VBA Excel

3 Answers 3

6 Comments

2 Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

2 Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related