logo ASAP Utilities

Excel tip: Show the progress of your macro's

Date: 5 september 2001

If you turn off the screen updating, and your macros takes some time to finish,
the user might think that the computer has stopped to respond. Because of this it's
a good programming rule to inform the user of the macro progress by displaying a
message on the statusbar at the bottom of the screen. Here is an example:


Copy-paste friendly code:
Sub DisplayProgressInStatusbar()
' Show an "in progress" message

' Makes sure that the statusbar is visible.
      Application.DisplayStatusBar = True

      ' Enter your message for the statusbar:
      Application.StatusBar = "Now processing...."

      ' -* Add your code here *-

      ' When your code is finished, reset the statusbar:
      Application.StatusBar = False
End Sub

Sub ShowProgressInLoop()
' Show the progress in a loop

      Dim rngCel                             As Range
      Dim lCounter                           As Long
      Dim lTotal                             As Long
      lTotal = Selection.Cells.Count            ' total amount of cells to walk through

      ' Makes sure that the statusbar is visible.
      Application.DisplayStatusBar = True

      For Each rngCel In Selection
            ' show the progress in the statusbar:
            Application.StatusBar = "Processing cell " & rngCel.AddressLocal & _
                                    "    " & Format((lCounter / lTotal), "0%")

            ' do something
            ' -* Add your code here *-

            ' update the counter
            lCounter = lCounter + 1
      Next rngCel


      ' When your code is finished, reset the statusbar:
      Application.StatusBar = False
End Sub



« back

Home Privacy Policy Cookie Policy EULA Download All added Excel tools Sitemap Contact Us


Empowering Excel Users Worldwide for 25 Years