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?
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 SubII 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 SubThe results
I made more than 10 tests launches per code, collected all the results in table and presented the summary at the graph below.
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.
