Questions similar to this appear on the Bentley Discussion Groups. This appeared in the VBA discussion group.

Q You are writing code that may take a long time to execute, and want to provide feedback to your users. You are looking for answers to questions like these …

• How do I show a Progress Bar to my users?
• How do I let a user cancel an operation?

A A solution to those questions is to pop a modeless UserForm with a Progress Bar. Put a Cancel button on there while you're at it, to let your user stop a lengthy task.

Unfortunately there's a catch, but it's one you can work around: VBA does not provide a Progress Bar control. However, there's one on your Windows system right now, and you can borrow it for your VBA project. VBA lets you add controls that are present in your computer. Microsoft provide many different types of control, usually bundled in a .dll or .ocx COM library. You can add these controls to your VBA project by taking the following steps …

1. Open a UserForm for editing
2. Right-click the controls toolbox to pop a menu with the Additional Controls… menu, or choose menu Tools|Additional Controls… from the IDE menu bar
3. The Additional Controls dialog opens. Browse the list of controls to find the Microsoft ProgressBar Control 6.0, check the box on the left, and press the OK button

4. You can see a new icon in the controls toolbox. Click the icon and draw a ProgressBar on your UserForm

Now you need some code. You can download the VBA project with the code shown here. Your worker module probably contains a loop, and inside that loop is a lengthy task. Each time your code goes through the loop, you want to increment the ProgressBar. You also want to check to see if the Cancel button was pressed. Here's some example code, with a trivial operation that does nothing but waste time …

Public Sub Main()

    Dim frm                                 As New frmProgressBar
    frm.Show vbModeless

    Dim bStop                               As Boolean
    bStop = False
    Const MaxCount                          As Long = 100
    '   Set the maximum value on the Progress Bar
    frm.FinalValue = MaxCount

    '   A variable that measures our progress
    Dim counter                             As Long
    counter = 0
    Do While (Not bStop And counter < MaxCount)

        Dim delay                           As Long
        Dim trivial                         As Long
        trivial = 0
        For delay = 1 To 500000
            trivial = CLng(1.17 + CDbl(trivial))
        Next delay

        counter = 1 + counter

        frm.Increment
        frm.ProgressBar1.Refresh
        DoEvents	'  Essential to let VBA respond to a user action
        bStop = frm.Cancel
    Loop

    If (bStop) Then
        MsgBox "Cancel Pressed", vbInformation Or vbOKOnly, "Operation Cancelled"
        ShowMessage "User cancelled operation", "User cancelled operation", msdMessageCenterPriorityWarning
    Else
        ShowMessage "Operation completed normally", "Operation completed normally", msdMessageCenterPriorityInfo
    End If

    Unload frm
End Sub

Without the DoEvents statement in the loop, your code won't know about the Cancel button. DoEvents momentarily stops your code while Windows catches up with itself. This give the ProgressBar an opportunity to redraw itself and, most importantly, lets VBA know whether the user wants to cancel.

Your UserForm also requires some code to monitor the Cancel button. This examples assume that you have placed a ProgressBar control with the name ProgressBar1. We set a flag to indicate that the user has pressed that Cancel button, with a read-only property that lets your main loop interrogate that flag …

Private m_bCancel                           As Boolean
' ---------------------------------------------------------------------
Public Property Get Cancel() As Boolean
    Cancel = m_bCancel
End Property
' ---------------------------------------------------------------------
Public Property Let FinalValue(ByVal v As Long)
    ProgressBar1.Max = CSng(v)
End Property
' ---------------------------------------------------------------------
Public Sub Increment()
    If (ProgressBar1.Value < ProgressBar1.Max) Then
        ProgressBar1.Value = ProgressBar1.Value + 1
    End If
End Sub
' ---------------------------------------------------------------------
Private Sub cmdCancel_Click()
    m_bCancel = True
End Sub
' ---------------------------------------------------------------------
Private Sub UserForm_Initialize()
    m_bCancel = False
    ProgressBar1.Min = 0
End Sub

Download Example VBA Project

A sample project is available. It includes code similar to that above and the progress form. You can download a ZIP file, and then extract ProgressBarExample.mvba to a folder that MicroStation can find. A good place to put it would be C:\Program Files\Bentley\WOrkspace\Standards\vba, or any other folder specified in the MS_VBASEARCHDIRECTORIES configuration variable.