Microsoft Office Tutorials and References
In Depth Information
Displaying a Progress Indicator
Displaying progress in the status bar
A simple way to display the progress of a macro is to use Excel’s status bar. The advantage is
that a status bar is very easy to program. However, the disadvantage is that most users aren’t
accustomed to watching the status bar and prefer a more visual display.
To write text to the status bar, use a statement such as
Application.StatusBar = “Please wait...”
You can, of course, update the status bar while your macro progresses. For example, if you have
a variable named Pct that represents the percent completed, you can write code that
periodically executes a statement such as this:
Application.StatusBar = “Processing… “ & Pct & “% Completed”
When your macro finishes, you must reset the status bar to its normal state with the following
statement:
Application.StatusBar = FalseIf you don’t reset the status bar, the final
message will continue to display.
A progress indicator will slow down your macro a bit because of the extra overhead of
having to update it. If speed is absolutely critical, you might prefer to forgo using a
progress indicator.
Creating a stand-alone progress indicator
This section describes how to set up a stand-alone progress indicator — that is, one that isn’t
initiated by displaying a UserForm — to display the progress of a macro. The macro simply clears
the worksheet and writes 20,000 random numbers to a range of cells:
Sub GenerateRandomNumbers()
‘ Inserts random numbers on the active worksheet
Const RowMax As Long = 500
Const ColMax As Long = 40
Dim r As Long, c As Long
If TypeName(ActiveSheet) <> “Worksheet” Then Exit Sub
Cells.Clear
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Next c
Next r
End Sub
 
Search JabSto ::




Custom Search