Show Message In Excel Status Bar

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

Mod Function

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?

__________________