Here's a long post of mine from another forum listing a bunch of
options.
There are many ways to create a progress bar.
1) The simplest way is to use the Application.Statusbar to provide a
progress report. Two examples of this follow.
Sub useStatusbar1()
Dim i As Long
For i = 1 To 10
Application.StatusBar = "Now on " & i & " out of 10"
'do stuff
Application.Wait (Now() + TimeSerial(0, 0, 1))
Next i
Application.StatusBar = False
End Sub
Sub useStatusbar2()
Dim i As Long
For i = 1 To 10
'do stuff
Application.Wait (Now() + TimeSerial(0, 0, 1))
Application.StatusBar = String(i, Chr(1)) _
& " (" & CInt(i / 10 * 100) & "%)"
Next i
Application.StatusBar = False
End Sub
2) If you must use a graphical display, there are three choices. The
first two use the ProgressBar control.
2.1) The first is to have the user click something on the chart that
initiates the process. For an example of that see the ProgressBar help
in CMCTL198.chm.
2.2) The only way to create a progress bar programmatically and update
it without user intervention is to create an asynchronous task. There
are two ways to do that. Note that while the examples below may look
simple, you are initiating parallel asynchronous tasks. This introduces
a degree of complexity that may be masked by VBA but which can come
back to haunt the inexperienced programmer.
2.2.1) Use a modeless userform. First, this requires XL2000 or newer.
Second, creating a modeless userform initiates a concurrent
asynchronous task. It becomes your responsibility to manage both your
primary code 'stream,' so to say, and this new asynchronous stream that
is displaying the progress bar.
Sub testProgBarModeless()
Dim i As Integer
With UserForm1
.Show vbModeless
.ProgressBar1.Value = 0
For i = 1 To 10
'do stuff
Application.Wait (Now() + TimeSerial(0, 0, 1))
.ProgressBar1.Value = CInt(i / 10 * 100)
Next i
.Hide
End With
End Sub
2.2.2) Use the OnTime method to intiate a asynchronous task. Something
along the lines of
Sub testProgBarModal()
Application.OnTime Now() + TimeSerial(0, 0, 3), _
"updateProgBarModal"
With UserForm1
.ProgressBar1.Value = 0
.Show
End With
End Sub
Sub updateProgBarModal()
'This is called for async processing by testProgBarModal
Dim i As Integer
With UserForm1
For i = 1 To 10
'do stuff
Application.Wait (Now() + TimeSerial(0, 0, 1))
.ProgressBar1.Value = CInt(i / 10 * 100)
Next i
.Hide
End With
End Sub
2.3) Create your own pictorial display and don't use the ProgressBar
control. There is no guarantee that the control will exist on every
machine on which the code will run. For an example of this you will
have to wait for me to create a tutorial on my web site that documents
all of the above and this additional technique.
--
Regards,
Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
