Poor Man's Status Bar in VBA

Mike Wolfe -

Here's the quickest and dirtiest status bar I've ever come up with.  

This is of no use to end users, but for developers it's a quick and easy way to make sure your long-running loop isn't hung up.

Debug.Print ".";: DoEvents

Let's break this down:

  • Debug.Print: sends output to the immediate window
  • ".": any single character will do
  • ;: special syntax that prevents Debug.Print from adding a line break
  • :: character used to combine multiple lines of code (keeps this a one-liner)
  • DoEvents: yields execution so that the immediate window can be updated

Sample usage

Sub TestPoorMansStatusBar()
    Debug.Print "Starting"
    Dim i As Long
    For i = 1 To 15
        Sleep 300
        Debug.Print ".";: DoEvents
    Next i
    Debug.Print "Done"
End Sub

Here is the sample output:

The other nice thing is that the included call to DoEvents ensures that you can Ctrl + Break to pause your loop if it ends up taking a lot longer than you expected.

Image by Christian Dorn from Pixabay