You are on the Home/Excel/Excel Tips/Progress Bar page
Google
Web This Site

Progress bar in VBA code

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.