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.
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
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.