Adding VBA Processing Status with a Cancel Option

Many applications provide a progress bar with a Cancel option for long running processes. VBA does not have a native progress bar control, but you can provide the same type of information with your own “Status” UserForm containing an informational label and a Cancel button.

CAD Training

The code below shows how this can be accomplished. The click event of the Cancel button will set a public Boolean variable (IsCancelled) to true, which is then picked up by the main processing loop and stops the execution.The key is to include calls to the DoEvents function inside your code. DoEvents temporarily suspends operation of the current module so Windows can receive and process other events. In this case we want Windows to update the label on our status form and process any click event on the Cancel button.

 

Main Module

Public IsCancelledAs Boolean
Sub TestCancel()
Dim i As Long
Dim iStart As Long
Dim iEnd As Long
iStart = 0
iEnd = 2147483647   ' largest possible Long value
With frmStatus
.lblStatus.Caption = "Initializing"
.Show False' Status form is non-modal
End With
IsCancelled = False
For i = iStartToiEnd
DoEvents
If IsCancelled Then
MsgBox "User cancelled at "&CStr(i), vbInformation
Exit Sub
End If
frmStatus.lblStatus.Caption = "Counting: " &CStr(i) & " of " &CStr(iEnd)
Next
Unload frmStatus
MsgBox "Processing Complete", vbInformation
End Sub
Forms - frmStatus
Private Sub cmdCancel_Click()
IsCancelled = True
Unload Me
End Sub

 

Calling DoEvents and updating the status label every time through the loop is a big performance drain on the application. Modifying the main processing loop so that the DoEvents and status update happen at only regular intervals will greatly speed up the processing of your application. In the modified snippet below DoEvents and the status update are done every 1000 times through the loop.

iStatus = 1000
For i = iStartToiEnd
If i Mod iStatus = 0 Then
DoEvents
If IsCancelled Then
MsgBox "User cancelled at "&CStr(i), vbInformation
Exit Sub
End If
frmStatus.lblStatus.Caption = "Counting: " &CStr(i) & " of " &CStr(iEnd)
End If
Next
Bob Mecham

Bob Mecham

Bob joined the training and consulting firm EnvisionCAD in 2000 and became a partner in June of 2002. He has specialized in the software MicroStation & InRoads from Bentley Systems, Inc. Mr. Mecham is an industry expert in the implementation, configuration, development, and customization of the two software applications MicroStation and InRoads. Bob instructs MicroStation classes ranging from fundamentals to advanced productivity. He has extensive experience using, implementing, and instructing both InRoads Survey and InRoads Site Design. Bob received his degree in Civil Engineering Technology from the Madison Area Technical College in 1992 and has over a decade of experience in the use, implementation, and management of software and CADD systems. He has worked for a wide range of organizations providing services from general survey and engineering services to specific applications such as landfills and site design.

Tags:

Leave a Reply

Your email address will not be published. Required fields are marked *