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!
hello, thank you again for your site which gives me new functionalities to add to my SAP code so that users can follow the progress. thanks again 👍.
I’m really happy that my articles can help 🙂