Excel

Use the Status Bar as a Progress Indicator

Ease of Use

Easy

Version tested with

2002 

Submitted by:

Jacob Hilderbrand

Description:

Use the Status Bar to show the user what the progress is in a long macro. 

Discussion:

You have a macro that takes some time to run. Maybe 30 seconds, maybe 10 minutes. At any rate you want the user to know that the macro is still running and what the progress is. A very simple technique to use is the Status Bar. 

Code:

instructions for use 

			
Option Explicit 
 
Sub StatusBar() 
     
    Dim x               As Integer 
    Dim MyTimer         As Double 
     
     'Change this loop as needed.
    For x = 1 To 250 
         
         'Dummy Loop here just to waste time.
         'Replace this loop with your actual code.
        MyTimer = Timer 
        Do 
        Loop While Timer - MyTimer < 0.03 
         
        Application.StatusBar = "Progress: " & x & " of 250: " & Format(x / 250, "Percent") 
        DoEvents 
         
    Next x 
     
    Application.StatusBar = False 
     
End Sub

How to use:

  1. Open Excel.
  2. Press Alt + F11 to open VBE.
  3. Insert | Module.
  4. Paste the code there.
  5. Close VBE (Alt + Q or press the X in the top right corner).
  6. From the Tools select Macro | Macros.
  7. Select "StatusBar" and press Run.
  8. Look at the Status Bar on the bottom of the screen.
 

Test the code:

  1. Refer to the "How To Use" section.
  2. Download the attachment for a working example.
 

Sample File:

StatusBar.ZIP 6.59KB 

Approved by mdmackillop


This entry has been viewed 739 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express