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!
I am going to close this one out and say Modal is the winner. I have tried both ways, but you end up trying to close too many loopholes with modeless userforms. Modal is more difficult because it is more strict, but it encourages you to break up your code into smaller chunks which is better in the long run anyway.
Actually you have following properties, resulting in pros/cons depending on your need:
If you want to block the UI AND let the caller continue, then you need to open the Form in modal mode with
Application.OnTime
.There's also a third way, using the
Application.StatusBar
. You can even simulate a true progress bar by using a sequence of U+25A0 and U+25A1 characters.I think that the initial topic is worth of replying since the question was formulated so nicely that google finds it first.
Section 1 - Theory
The first thing to say is that to transfer the variables between the modules is not difficult at all.
The only thing you need to do is to create a separate module and put there all the global variables. Then you will be able to read them everywhere in all forms, sheets, modules.
The second thing is the window should be a MODELESS. Why that? The answer is to keep the mobility of the code, i.e.
This is a great advantage to be versatile here.
Section 2 - Practice
1) Create a module "Declaration" with the global variables:
Public StopForce As Integer 'this variable will be used as an indicator that the user pressed the cancel button
Public PCTDone As Single ' this is the % of the work that was done already
Public CurrentFile As String ' any other parameter that we want to transfer to the form.
2) Create the form with the button. In OnClick event of the button there should be a code where we refer to the global variable StopForce in Declaration module
3) Add one procedure where you update the progress bar
4) in any other module we must have the functions or the procedure/sub where the routine is done:
Definately Modal. If you are going to consider Modeless, you ought to run it on a seperate out-of-process thread and not on the Excel.exe main thread.