Is it better to show ProgressBar UserForms in VBA as modal or modeless?

Question

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!

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

1. the function where the most routine process is executed is not to be located in the UserForm module
2. you can call the window with progress bar from everywhere and
3. the only connection between the routine function/procedure are the global variables

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

 Private Sub CommandButton1_Click()

 Declaration.StopForce = 1
  End Sub

3) Add one procedure where you update the progress bar

Sub UpdateProgressBar(PCTDone_in As Single)
With UserForm1
    ' Update the Caption property of the Frame control.
    .FrameProgress.Caption = Format(PCTDone_in, "0%")
    ' Widen the Label control.
    .LabelProgress.Width = PCTDone_in * _
        (.FrameProgress.Width)
    ' Display the current file from global variable   
    .Label1.Caption = Declaration.CurrentFile
End With
End Sub

4) in any other module we must have the functions or the procedure/sub where the routine is done:

 For i=1 to All_Files

 Declaration.CurrentFile = myFiles (i)

 FormFnc.UpdateProgressBar (i / .Range("C11").Value)


 DoEvents

 If Declaration.StopForce = 1 Then
    GoTo 3
 End If

 Next i