Excel exposes the StatusBar object so we can communicate with our users via a little message in the lower left-hand corner of the screen.
Here's the default message you see in the StatusBar.
Today, I 'll show you how to show a Progress Meter in the status bar.
.
Why would you care? Well, when you force your users to sit through any multi-step process that takes a few moments to run, it's only polite to tell them what's going on. The StatusBar is one way to do that.
.
To get a progress meter into the StatusBar,you'll need a bit of VBA. Enter this code into a standard module and run the 'ShowProgress' macro.
' make StatusBar visible
Application.DisplayStatusBar = True
'First Message
Application.StatusBar = String(5, ChrW(9609)) & "Working..."
Application.Wait Now + TimeValue("00:00:02") '<-- Replace this line with your own code to do something
'Second Message
Application.StatusBar = String(10, ChrW(9609)) & "Still Working..."
Application.Wait Now + TimeValue("00:00:02") '<-- Replace this line with your own code to do something
'Last Message
Application.StatusBar = String(15, ChrW(9609)) & "Almost Done..."
Application.Wait Now + TimeValue("00:00:02") '<-- Replace this line with your own code to do something
'Relinquish the StatusBar
Application.StatusBar = False
End Sub
.
If all goes well, you should see something like this:
.
For the progress meter, we use the VB String function to repeat a defined character a given number of times. By entering String(5, ChrW(9609)), I'm telling Excel to repeat the block character 5 times. Everything else in the code should be self explanatory.
.
I like this technique because it plays into the thought that we're all monkeys, happy to wait as long as we have something to look at.
I often use this technique when refreshing large Query tables or multiple XML Maps....for the monkeys.
.
***Update:
Ross reminded me that Andy Pope has an Example Workbook that shows you different ways to show progress meters. In that workbook, he has an example where he uses the status bar to show a progress meter.
Hi, yeah this is a nice approach, who done it first was it Andy? Also Ivan did something with a control which is nice too:
http://www.xcelfiles.com/ProgressBar.html
thanks Mike
Ross
Ross: I forgot about Andy's workbook. I have run across his examples a long while back. I've put an update to the post. Thanks.
Ivan's example is waaaay too complicated for me. I can't see me turning the ActiveX progress bar in Excel - not for the work I do.
Besides, Ivan is smarter than I am.
Jamie Collins once posted a question in the NGs as to the handle for the Excel 2007 statusbar that I helped him with. When I asked him what he wanted it for he said it was to create a stacked progress bar in the status bar. I never saw the final product, but it sounded way cool.
This is a lot cooler when you use the RANDBETWEEN and VLOOKUP functions and have a list of 100 random facts that are selected and displayed. People get a kick out of little tricks like that and its more entertaining than watching %'s tick up.
Nice post. One thing though: make sure your code does not try to update the progress bar too much. If your loop runs 100,000 times, updating the PB that many times will make your code run much,much slower. Instead, include a small test, like
If LoopCount Mod 1000 = 0 Then
'Now update PB
End If
Thanks for the mention Mike and Ross, but I doubt I was the first
Personally I prefer a progress meter to have a fixed width so I know where the end is and also the descriptive text does not creep.
Sub ShowProgress()
Dim strBar As String
Dim lngLoop As Long
' make StatusBar visible
Application.DisplayStatusBar = True
strBar = String(0, ChrW(&H25A0)) & String(10, ChrW(&H25A1))
Application.StatusBar = strBar & "Starting..."
Application.Wait Now + TimeValue("00:00:01")
For lngLoop = 1 To 10
strBar = String(lngLoop, ChrW(&H25A0)) & String(10 - lngLoop, ChrW(&H25A1))
Application.StatusBar = strBar & " Processing..."
Application.Wait Now + TimeValue("00:00:01") '<-- Replace this line with your own code to do something
Next
'Relinquish the StatusBar
Application.StatusBar = False
End Sub
Andy: Great idea!
"I often use this technique when refreshing large Query tables "
Is is possible to create a progress bar that moves proportional to the records that are pulled by a query ????... So it should say 100 of 100000 records pulled or something similar
Sam: It is possible, but I find that type of progress indicator to be relatively process-intensive. That is to say, the constant checks an looping that need to be done while a query is running can slow down the process.
I typically keep progress meters reserved for the multi-step stuff.
I must say the above code is the best and most easy to understand code out there to monitor status for long runnung jobs (without a loop).
Works like a charm!
Thank You.
The Application.Wait command pauses my refreshing query which in turn completely defeats the purpose of the status bar in the first place...
Did i do it wrong?