Bit of a fun Macro today, which should cater for any impatient Excel users that you may have. I am going to show you how to display a progress bar whilst any other Macros are updating.
What Does The Macro Do?
This Macro will display a progress bar whilst your other Macro is busy running, this is really useful if
- Your Macro takes a while to run
- You do not want your users thinking that nothing is happening
In this example we will combine it with some dummy code that takes a while to execute ( a counter of 1 to 500) and will display a % of the count whilst it is running until 100% or 500 is reached.
How Does It Work?
FIRST. Open Visual Basic – by hitting ALT +F11 or Developer Tab
Step 1. The first message we present to the user in the status bar is Percent Completed
Step 2. This is our dummy or example macro, we are counting from 1 to 500 in cell B5
Step 3. We display in the status bar the progress of the counting of 1 to 500 by instructing it to divide the value of i by 500 and display it as a percentage.
Step 4. At the end of the code we display the status as “Finished”.
Step 5. Test Your Macro!
The Progess Bar will update as the Macro progresses as you can see in my example below.
Want To Copy The Code?
‘macro by howtoexcelatexcel.com
Application.StatusBar = “Percent Completed”
For i = 1 To 500
Cells(5, 2).Value = i
Application.StatusBar = “Percent Completed:” & Format(i / 500, “0%”)
Application.StatusBar = “Finished”
If you want more Excel and VBA tips then sign up to my monthly Newsletter where I share 3 Excel Tips on the first Wednesday of the month and receive my free Ebook, 30 Excel Tips.
If you want to see all of the blog posts in the Macro Mondays Series or the example worksheets you can do so by clicking on the links below.