Table of Contents
StatusBar
The StatusBar property of the Application object in Excel VBA can be used to indicate the progress of a lengthy macro. This way you can let the user know that a macro is still running.
Situation:
The macro we are going to create fills Range(“A1:E20”) with random numbers.
Add the following code lines to the command button:
1. First we declare three variables of type Integer named i j and pctCompl.
Dim i As Integer j As Integer pctCompl As Integer
2. Add a Double Loop.
For i = 1 To 20
For j = 1 To 5
Next j
Next i
Add the following code lines (at 3 4 and 5) to the loop.
3. Use the RandBetween function to import a random number between 20 and 100.
Cells(i j).Value = WorksheetFunction.RandBetween(20 100)
4. Initialize the variable pctCompl. The second code line writes the value of the variable pctCompl and some descriptive text in the status bar.
pctCompl = (i – 1) * 5 + (j * 1)
Application.StatusBar = “Importing Data.. ” & pctCompl & “% Completed”
Example: For i = 3 j = 1 (3 – 1) * 5 + (1 * 1) = 11% has been completed.
5. We use the Wait method of the Application object to simulate a lengthy macro.
Application.Wait Now + TimeValue(“00:00:01”)
6. To restore the default status bar text set the StatusBar property to False (outside the loop).
Application.StatusBar = False
Result when you click the command button on the sheet:
Note: You can interrupt a macro at any time by pressing Esc or Ctrl + Break. For a more visual approach see our Progress Indicator program.
