There are many ways to create a progress bar.
In each of the examples below, the code that would do the actual processing should replace the fragment:
'do stuff Application.Wait (Now() + TimeSerial(0, 0, 1))
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.