Is it better to show ProgressBar UserForms in VBA as modal or modeless? What are the best practices for developing progress indicators in VBA?
Modeless UserForms require the use of Application.Interactive = False
, whereas Modal UserForms by their very nature block any interaction with the application until the core procedure has finished, or is cancelled.
If Application.Interactive = False
is used, however, the Esc key interrupts code execution, so the use of Application.EnableCancelKey = xlErrorHandler
and error handling (Err.Number = 18
) is required in both the UserForm and the calling procedure.
Resource intensive calling procedures can also result in CommandButton_Click
and UserForm_Activate
events misfiring in modeless UserForms.
In general, progress indicators that use modal UserForms seem simpler, because the code that is being executed is fully contained in the UserForm module, and there is less need for passing of variables.
The problem, however, with using modal UserForms for progress indicators is that a separate UserForm module is required for every procedure that needs a progress indicator, because the calling procedure has to be inside the UserForm_Activate procedure.
So, while it is possible to have a single reusable progress indicator in a modeless UserForm, it will be less reliable than executing the code from within multiple modal UserForms.
Which way is better?
Thanks!