Progress bar refresh – macro performance

In my previous post I already described how to create simple progress bar and how to implement it into code. It is so much better to watch the macro performance, than freezed screen. In case You have to stop macro, it is even easier to do this with bar, than without it. But I have never thought about what impact have that visualization of progress bar on macro performance.

I was always thinking about it as a good thing, which was helping me. So I was treating it like a friend or even like a good friend and was using him almost in every macro.

But there came a time, when I realized that things were not like I was thinking. It was back then when I implemented progress bar in, let’s say simple macro, which was executing in a relatively short period of time.

After implementation that relatively short period went into 10 times longer period. I was in kinda shock.

My friend, progress bar, betrayed me.

OK, that showed macro performance, but with what cost?!

I realized that I need to rethink my relationship with that. Maybe watching the progress is not always a good idea?

First of all, let’s prove that it is slowing down the macro, on the simple example. For the purposes of comparison I’ll use code from my previous post https://simpleexcelvba.com/array-vs-table-fill-color-property-speed-comparison/ – I have table 10 columns, 100 000 rows filled with different numbers and colors.

Option Explicit

Sub main2()

Dim lastRow As Long
Dim lastCol As Long
Dim ws As Worksheet

Dim comper As Range
Dim j As Long, i As Long
Dim clock As Double

clock = Timer
Application.ScreenUpdating = False
Set ws = Sheet1 'set your worksheet

'Progression.Show

With ws
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    lastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
    
    For i = 1 To lastRow
        
        For j = 1 To lastCol
            
            If (.Cells(i, j) Mod 2) = 0 Then
                .Cells(i, j).Interior.ColorIndex = 3
            Else
                .Cells(i, j).Interior.ColorIndex = 4
            End If
        
        Next
        'Call progress(i, lastRow)
    Next

End With

'Unload Progression

Debug.Print Round(Timer - clock, 3)
Application.ScreenUpdating = True

End Sub

Results & conclusions

After executing macro with out-commented lines macro all takes
15 seconds.
When executing macro with progress bar on all takes almost
40 seconds.
It’s not 10 times longer as I was saying at the beginning, because it’s a simple code in the loop, but… still much, much longer!

Now, back to the question if it’s a good idea or not to use progress bar.

I rethought our relationship, with bar, and found out that in large number of iteration I don’t really need to see every digit. Still want to observe macro performance, but I could see the progress on the bar gradually. So I implemented something like this to the code:

        If (i Mod 1000) = 0 Then
            Call progress(i, lastRow)
        End If

Miracle! With that small trick I am able to still observe performance and keep almost same time like without progress bar! My friend progress bar is still my friend, as in good old days!