Enhanced Datasystems - Excel: Progress Bar

A Progress Bar Class

You can download a ready built multi-purpose progress bar class by clicking here for XL2000 and above or clicking here for Excel 97*. This now works in all versions of Excel from 97 on, but in Excel 97 rather than showing a form, messages are written to the status bar. That's not ideal, but it means that one set of code can be used with the confidence that it will work regardless of the machine it is running on.

To use the class in your projects, copy the clsProgBar and frmProgress modules into your workbook.

Thanks to Jamie Collins for the technique used to do the coloured bar.

Using the progress bar is extremely simple as illustrated by the following code. The demo version of the file now also demonstrates how to handle the user pressing cancel during execution of your routine.

Public UserCancelled As Boolean

Sub ProgBarDemo()
Dim PB As clsProgBar
Dim nCounter As Integer
Dim lWaitCount As Long

Set PB = New clsProgBar

With PB

.Title = "Enhanced Datasystems Progress Bar"
.Caption2 = "This is caption 2"
.Caption3 = "This is caption 3"
.Show

For nCounter = 0 To 100

.Progress = nCounter
.Caption1 = "Progress message " & CStr(nCounter)

For lWaitCount = 0 To 1000000

If UserCancelled = True Then GoTo EndRoutine

Next lWaitCount

Next nCounter

EndRoutine:

.Finish

End With

Set PB = Nothing

End Sub

*there is no difference in the code of the two versions, but the 97 version has no digital signature to avoid problems viewing the code in the Visual Basic Editor