If a macro takes a long time to run, it can be frustrating to wait for it to finish. Usually the screen updating is turned off when a macro runs, so it’s hard to tell if anything is happening. Maybe it stopped, and you’ll be sitting here for the rest of the day, blissfully unaware of the problem.
To help you stay informed, you can show messages in the status bar, to let you know what progress is being made. It sounds like a technical challenge, but it’s easy to do.
Long Slow Macro
When you build a macro, usually you do your best to make it as speedy as possible. However, some macros will inevitably be slow, and it’s nice to know if they are still crawling along, or if they curled up in the corner and died.
For this example, I build a little macro that should run slowly — it checks 100K rows of data, and enters a value in the adjacent cell. I’m sure there are ways to make this run faster, but for this exercise we’ll just focus on how to show a message in the status bar.
To test the code, you can create a workbook with numbers in cells A2:A100000. I used the Randbetween function to create the numbers, with 1 as the Minimum and 1000 as the Maximum, and then copied the data and pasted as values.
Original Macro Code
Here is the macro, before adding the status bar messages. It doesn’t really take too long to run, but should give you time to see the status bar messages changing, when we add those.
Sub GroupNumbers_Orig() Dim ws As Worksheet Dim rngSel As Range Dim c As Range Dim lCol As Long Dim lRow As Long Dim lRowEnd As Long Dim strGroup As String Dim lRowCurr As Long Set ws = ActiveSheet lRow = 2 lCol = 1 With ws lRowEnd = _ .Cells(.Rows.Count, lCol) _ .End(xlUp).Row Set rngSel = _ .Range(.Cells(lRow, lCol), _ .Cells(lRowEnd, lCol)) End With For Each c In rngSel strGroup = "" Select Case c.Value Case Is > 750 strGroup = "A" Case Is > 500 strGroup = "B" Case Is > 250 strGroup = "C" Case Else strGroup = "D" End Select c.Offset(0, 1).Value = strGroup Next c End Sub
Add Status Bar Messages
As I said earlier, it’s easy to add a few messages in the status bar, so you can keep an eye on the macro’s progress. The extra bit of code might slow the macro down a little more, but it’s usually a good tradeoff – a few seconds slower, for a little peace of mind.
To show a simple message in the status bar, you can add a line of code like this:
Application.StatusBar = "Starting the update"
You could scatter a few messages like that throughout the code, announcing the start of each section. For example:
Application.StatusBar = "Uploading records to database"
Then, at the end of the macro, use this line of code to give control of the status bar back to Excel:
Application.StatusBar = False
Show Messages Based on Row Number
In this example, the code runs down thousands of rows of data. It’s done in one block of code, so there aren’t any sections where a simple message could be shown.
So, for this macro, I added a row number check, at the beginning of the “For each c…” loop
For Each c In rngSel If c.Row Mod 10000 = 0 Then Application.StatusBar = _ "Updating Row " & Format(c.Row, "#,##0") _ & " of " _ & Format(lRowEnd, "#,##0") & " rows" End If
In the code, the Mod function shows the remainder after the row number is divided by 10000. For rows with a zero remainder, the message is shown. It shows the current row number, and the total number of rows in the range. The numbers are formatted with a comma separator. For example, this message would show at the 40,000 row mark.
Updating Row 40,000 of 100,000 rows
Do You Show Messages?
For long-running macros, do you show messages in the status bar? If so:
- Do you find the messages helpful?
- Do you notice much effect on the speed, when showing messages?
- What technique do you use to trigger the messages?