Adding VBA Processing Status with a Cancel Option

March 27, 2012

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.

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.

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.