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
When executing macro with progress bar on all takes almost
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!
3 thoughts on “Progress bar refresh – macro performance”
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 🙂