Poor Man's Status Bar in VBA

If you're looking for a quick and dirty way to keep track of a long-running process while developing, this VBA one-liner will do the trick.

Poor Man's Status Bar in VBA

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

All original code samples by Mike Wolfe are licensed under CC BY 4.0