Excel VBA Macro: Progress Bar While Macro is Running (UserForm). In this video, we go over how to create a status bar that serves as a progress indicator for any users running your macro. The progress bar we make is created with a UserForm that updates as the macro continues to run. Each update shows an increase in bar width and percentage. We also briefly go over the importance of DoEvents and vbModeless
Data used in this video:
simplemaps.com/data/world-cities
Code (RU-vid doesn't allow brackets; so LT and GT are used for less than and greater than, respectively):
Sub delete_rows()
Dim count As Integer
Dim start As Integer
Dim i As Integer
count = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
i = 2
OpenStatusBar
Do While i LT= count
If Cells(i, 9) = "" Then
Rows(i).EntireRow.Delete
i = i - 1
End If
DoEvents
Call RunStatusBar(i, count)
i = i + 1
count = ActiveSheet.Cells(Rows.count, "A").End(xlUp).row
Loop
Unload StatusBar
End Sub
Sub OpenStatusBar()
With StatusBar
.Bar.Width = 0
.Frame.Caption = "0% Complete"
.Show vbModeless
End With
End Sub
Sub RunStatusBar(row As Integer, total As Integer)
With StatusBar
.Bar.Width = 246 * (row / total)
.Frame.Caption = Round((row / total) * 100, 0) & "% Complete"
End With
End Sub
#ExcelVBA #ExcelMacro
1 июл 2024