Hello, Excellers. Bit of a fun Macro today, which is also very useful. If you’ve ever written a macro in Excel, you know there’s no built-in way to display a progress message in the status bar. Knowing the progress of the VBA macro is helpful for users who have no idea how long their Macro will take to run. In this blog post, I will show you how to write a VBA macro that displays a progress message in the status bar so your users always know what’s going on. So stay tuned while I demonstrate how to display a progress bar while any other Macros are updating.
What Does The Macro Do?
This Macro will display a VBA progress bar whilst your other Macro is busy running. The progress bar is useful if
- Your Macro takes a while to run
- You do not want your users thinking that nothing is happening
In this example, I combine the VBA progress bar with some dummy code. This sample code takes a while to execute (a counter of 1 to 500). The Macro displays a % of the progress of the count while running until 100% or 500 is reached.
How Does It Work?
Step 1. Insert A New Module. And Name The Macro
First, you will need to open the Visual Basic Editor. There are two ways to do this. Either by hitting ALT +F11 or selecting the Developer Tab | Code Group | Visual Basic. Both methods have the same result. As this code is specific to this workbook, I am creating. I will insert the module in the current workbook. This Macro is named UpdateStatusBar. Note that Excel inserts the End Sub line of code at this point. All other lines of code belong between these first two lines.
Sub updateStatusBar()
End Sub
Step 2. Application.StatusBar Property.
The second step in this VBA code is to make use of the Applications.StatusBar property. This returns or sets the text in the status bar. This is therefore used to display the text of of the progress of the VBA macro. This particular line of code displays the first part of the message to the status bar at the bottom of the Excel window.
Application.StatusBar = "Percent Completed"
Step 3. Dummy Macro. Counting From 1 To 500.
This is our dummy or example macro, we are counting from 1 to 500 in cell B5. I have used the For Next loop to count from 1 to 500. FOR LOOP loops through the range of cells and perform a specific task until the condition is TRUE. In this example Excel counts form 1 until 500 is reached. The cell B5 contains the counter.
For i = 1 To 500
Cells(5, 2).Value = i
Step 4. Display The Status Bar Progress.
The next line of code displays the status bar progress, achieved by a simple calculation. The value of (i) is divided by 500. The result of the value is displayed as a percentage. The value of (i) changes with every loop, and so does the percentage displayed. Excel loops from VBA code lines 4 to 6 until (i) reaches 500.
Application.StatusBar = "Percent Complete:" & Format(i / 500, "0%")
Next
Step 5. Display ‘Finished’
As soon as the counter reached reaches 500, the status bar will display ‘Finished’.
Application.StatusBar = "Finished"
Step 6. Ending and Testing Your Macro.
Finally, the macro ends when all loops are finished and the status bar displays ‘Finished’. All that is left is the best part right. Test Your Macro!.
End Sub
The Progess Bar will update as the Macro progresses as you can see in my example below.
Want To Copy The Code?
Sub updateStatusBar()
'macro by howtoexcelatexcelcom
Application.StatusBar = "Percent Completed"
For i = 1 To 500
Cells(5, 2).Value = i
Application.StatusBar = "Percent Complete:" & Format(i / 500, "0%")
Next
Application.StatusBar = "Finished"
End Sub
*Disclaimer. Feel free to copy the code above to try this Excel VBA macro yourself. Always back up your work before running any code!.
That’s all there is to it! You can now write your own VBA macros to display progress messages in the status bar. If you want more help with Excel or VBA, be sure to check out our other tutorials. And as always, feel free to reach out if you have any questions. Thanks for reading!
Let me know in the comments below if you use an Excel VBA progress bar with your Excel VBA macros. If you have share the results. Did the end users find them useful?.
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, 50 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.
How To Excel At Excel – Macro Mondays Blog Posts.
