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!

Staples

Answer 1

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.

Staples

Answer 2

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.

Namecalling

Thanks for the suggestion GSerg, but I have tried using that in the past, but Excel 2007 stops updating the window and shows "Not Responding" at the top of the application window. – Staples

I'm not too experienced with office 2007, but what if you call DoEvents right after setting a value to StatusBar? – Namecalling

Huh, that worked. Wow, that's a nice option to have isn't it! Thanks GSerg! – Staples

One problem with using the StatusBar for progress indicators is there is no way to provide a cancel button. – Staples

nice technique but it doesn't really answer a best practise question on modal/modeless, with a technical answer on ausing statusbar. – Avoirdupois

problem with DoEvents is that the user can iteract with the spreadsheet while the macro is running and this is a potential for problems... not cool! – Chemist

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.

Avoirdupois

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

Duhl

Please note that modeless UserForms seem not to work at all on Mac OS – Yorkist

Answer 5

Actually you have following properties, resulting in pros/cons depending on your need:

Type      | Impact on UI | Impact on caller execution
----------|--------------|-----------------------------
Modal     | Blocked      | Blocked until Form is closed
Modeless  | Not blocked  | Continues

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.

Quillet

I get a error message trying to open the form via Application.OnTime (I tried different variations). What syntax are you using to to this? – Kura