Looping performance – all in one or separated loops?

Recently I was working on complex tool, which was doing some actions on Excel cells. In some cases it took a while to process whole example data workbook. Thinking how to speed up the code I asked myself what will be better in case of looping performance – all procedures in one loop or all in separated loops?

Looping performance - all in one or separated offspring

The only solution for this question was to check that on my own. I needed to do some tests.

Test case

To keep it simple the test case was to fill the cells with random values (Rnd function) and check every second column if the values are equal in previous column in the same row. Also I set up the range of 1000 x 1000 cells.

Modules

That’s why I prepared 2 code modules. The first one with separated processes and the second having all in 1 loop (not separated).

I separated

The code includes Timer and the progress bar. There are 2 double loops responsible for filling in the cells with numbers and filling with color based on the conditional in every second column.

Option Explicit

Sub val_mach()

Dim ws As Worksheet
Dim i As Long, j As Long, x As Long, y As Long
Dim t1 As Double
Application.ScreenUpdating = False

x = 1000
y = 1000

Set ws = ThisWorkbook.Sheets(1)
ws.Range("A1").CurrentRegion.Clear

t1 = Timer
Progression.Show

With ws
    Progression.Text2.Caption = "1/2"
    For j = 1 To x
        For i = 1 To y
            .Cells(i, j) = Int(1 + Rnd * (100 - 1 + 1))
        Next
        Call progress(j, x)
    Next
    
    Progression.Text2.Caption = "2/2"
    For j = 1 To x Step 2
        For i = 1 To y
            If .Cells(i, j) = .Cells(i, j + 1) Then
                .Cells(i, j).Interior.ColorIndex = 4
            Else
                .Cells(i, j).Interior.ColorIndex = 5
            End If
        Next
        Call progress(j, x)
    Next
End With

Unload Progression
Application.ScreenUpdating = True
Debug.Print "Separated: " & Format(Timer - t1, "0.00")

End Sub

II not separated

In this case there is only 1 double loop includes both filling with values and colors.

Option Explicit

Sub not_sep()

Dim ws As Worksheet
Dim i As Long, j As Long, x As Long, y As Long
Dim t1 As Double
Application.ScreenUpdating = False

x = 1000
y = 1000

Set ws = ThisWorkbook.Sheets(1)
ws.Range("A1").CurrentRegion.Clear

t1 = Timer
Progression.Show

With ws
    Progression.Text2.Caption = "1/1"
    For j = 1 To x
        For i = 1 To y
            .Cells(i, j) = Int(1 + Rnd * (100 - 1 + 1))
            If j Mod 2 = 0 Then
                If .Cells(i, j) = .Cells(i, j - 1) Then
                    .Cells(i, j).Interior.ColorIndex = 4
                Else
                    .Cells(i, j).Interior.ColorIndex = 5
                End If
            End If
        Next
        Call progress(j, x)
    Next
    
End With

Unload Progression
Application.ScreenUpdating = True
Debug.Print "Not separated: " & Format(Timer - t1, "0.00")

End Sub

The results

I made more than 10 tests launches per code, collected all the results in table and presented the summary at the graph below.

Looping performance - all in one or separated?

As You can clearly see the average time for the separated processes was almost 4 seconds shorter than for the not separated module. So separated module is almost 10% faster than II case.

Final words

Honestly, I was not so surprised as I could be. Somewhere, deep inside me I felt that if I separate those actions, it can work faster in case of performance.

There are many ways of creating the code. Simple and complex, slower and faster approaches. It’s up to You, which one You’ll choose. Now You also know that there can be the difference in time according to looping performance. And it depends, among other things, on having all processes in 1 loop or keeping them separated.

Author: Tomasz Płociński

I'm very advanced in VBA, Excel, also easily linking VBA with other Office applications (e.g. PowerPoint) and external applications (e.g. SAP). I take part also in RPA processes (WebQuery, DataCache, IBM Access Client Solutions) where I can also use my SQL basic skillset. I'm trying now to widen my knowledge into TypeScript/JavaScript direction.

Leave a Reply

Your email address will not be published. Required fields are marked *