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.

Show Message in Status Bar http://blog.contextures.com/

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"

statusbarmessage01

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

statusbarmessage02

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?

__________________

Show Message in Status Bar http://blog.contextures.com/

You may also like...

11 Responses

  1. Graham says:

    Your statement, “I’m sure there are ways to make this run faster” – Yes, use arrays. I have a workbook that has >250k rows of data, using the individual cell update method, described here takes about 30 seconds on my machine on my workbook. Using and array to read in the complete range, do the calculations and then paste back the updated array to the worksheet, about three seconds

  2. AlexJ says:

    Hi Debra,
    I use this all the time. To make it easier to use, I created a function called “sBar()” which is in my standard library to do this. Triggering techniques vary widely by application, but this is an easy way to help users (or me) monitor progress.

  3. Stewart Alcock says:

    Hi Debra,
    I’ve been using for some years a “Progress Indicator” courtesy of J-Walk.
    You’ll find it here http://spreadsheetpage.com/index.php/site/tip/displaying_a_progress_indicator/
    Does basically the same but on a form, not the Status bar; slows it down a little bit.
    I’ll try the Status bar trick from now on.

  4. StickyLabels says:

    If StatusBar updates are not showing correctly – this usually happens when Excel is feeling particularly taxed – then stick a line of code with “DoEvents” after the StatusBar update line and it should show your progress correctly.

  5. Omar says:

    My question is whether your users know the status bar exists! I’m quite sure most of ours would never see the updated progress indicators.

    On one particularly slow report, I added a message box (simple information, no progress bar) so the users don’t think the thing is frozen. Later, I set up an early refresh using Task Scheduler outside of business hours as that report doesn’t change through the day anyway.

  6. B.Frassek says:

    A nice way to indicate that a calculation is in progress.
    However, to speed up the calculation of a big matrix, I’m executing this Routine before the calculation starts:

    Sub turbo_on()
    With Application
    .ScreenUpdating = False
    ‘.EnableEvents = False
    .Calculation = xlCalculationManual
    .DisplayStatusBar = False
    End With
    ActiveSheet.DisplayPageBreaks = False

    End Sub

    and afterwards the opposite of the commands within “turbo_on”.
    With these routines calculation needs about 1-2 minutes.

    Up to now I have no idea to show what’s going on.
    Althoungh observing the rotating busy Icon is somehow “meditational”, a good indicator would be better …

  7. Excel Whizz says:

    This is great Debra.

    I’ve been using a ‘Loading – Please Wait’ message which is just a button that pops up at the start of the routine and becomes invisible at the end but this looks takes it to the next level and looks much more professional!

Leave a Reply

Your email address will not be published. Required fields are marked *