Tag Archives: UserForm

MsgBox vs InputBox vs UserForm

VBA MsgBox vs InputBox vs UserForm

1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)
Loading...

One of the most common mistakes in VBA is using incorrect forms/dialogs for usually simple tasks. The VBA UserForm is one of the most exploited VBA modules I have ever seen (as opposed to VBA Class modules which are underutilized ;)). VBA developers and Excel users seem to think often that every dialog box and form needs to have that personal touch. The effect is often the opposite – with users being confused or experiencing errors with custom UserForms without proper error handling.

Today I want to make it easier for all those VBA users out there.

The form/dialog decision tree

Use the guide below when making your decision on whether you need that UserForm or if you are using that Messsage Box or InputBox in the right case:
VBA Message Box is

Decision Tree: MsgBox vs InputBox vs UserForm
Decision Tree: MsgBox vs InputBox vs UserForm

When to use Message Boxes?

Use the VBA MsgBox in the following situations:

  • Short message / information / warning – you want to present the user with a simple message be it an error, message or a warning
  • Yes or No decisions – ask the user if he/she agrees to your questions/ask or if the macro is to proceed etc.

Don’t use the VBA MsgBox in the following situations:

  • Long text messages – when wanting to display long messages or text

When to use the InputBox?

Use the VBA InputBox in the following situations:

  • Simple single variable input – you want the user to input a simple variable such as age, length etc.

Don’t use the VBA InputBox in the following situations:

  • Yes or No / Boolean variables – don’t ask the user to type in yes or no, 1 or 0 or similar boolean variables. Use a MsgBox instead.
  • Enumerations – don’t ask the user to input an item from a discrete list of values. Use a UserForm and a dropdown or listbox instead

When to use the UserForm?

Use the VBA UserForm in the following situations:

  • Complex input / select / interaction – use the UserForm when other options fail and simply a MsgBox or InputBox won’t do the trick

Don’t use the VBA UserForm in the following situations:

  • Simple single variable input – need the user to input an age or length value? Why not use the InputBox instead?
  • Yes or No / Boolean variables – use the MsgBox instead

Agree or disagree? Let me know in your comments!

vba progress bar

Animated VBA progress bar for Excel and Access

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 3.67 out of 5)
Loading...

Sometimes there are very large and complex solutions built in Excel (which is a mistake mind you), where calculations or macro executions can take minutes or even hours. This causes many issues, especially for the end users who usually do not know how long processing the calculations/macros will take. In such cases it is important to notify the end users of the progress of your macros/calculations so they can switch to other activities. This is where the VBA Progress bar can aid you.

For one of my older projects I needed a VBA Progress Bar that would show:

  1. The current progress of the computations
  2. How much execution time was left (estimation)

Users especially wanted to know how much execution time was left – whether they should grab a coffee or stay and wait for the macro to finish.

Option 1: Animated VBA Progress Bar UserForm

The easiest approach to animating an Excel is to create a simple UserForm with the use of a label control which width you can manipulate to show the current progress. Easy and straightforward.

Example of how to use the VBA Progress Bar in Excel:

Sub ExampleProgressBar()
    Dim pb As ProgressBar
    Set pb = New ProgressBar
    pb.Initialize "My title", 100
    'Add 10% progress
    pb.AddProgress 10
    '...
    'Hide and remove the Progress Bar
    pb.Hide
    Set pb = Nothing
End Sub

The result:

Advanced VBA Progress Bar
Advanced VBA Progress Bar

Download the Progress UserForm complete with sourcecode here:

Option 2: Animated Worksheet VBA Progress Bar

The UserForm progress bar is very good to use when you don’t want to show too much content or use advanced formatting. However, if you want the Progress Bar GUI to be more attractive I would suggest to go with a Worksheet Progress Bar. This gives you unlimited possibilities of how to make your Progress Bar more visually attractive and allows you to use Charts/Conditional formatting etc. My example below calculates the expected time left to complete a given task based on historical progress (forecasting based on how much time to make certain progress). Pretty cool and useful in my opinion when dealing with long lasting VBA Macros.

Excel VBA Progress Bar
Excel VBA Progress Bar

Below feel free to download a Workbook with the Excel Worksheet ProgressBar VBA:


Now how does it work? The progress bar is located on a separate hidden worksheet which appears only when the progress bar is activated. The estimation of the time left is extrapolated based on the time which elapsed to the current progress and will become more accurate with time – depending on how comparative each increment is.

See the example below of how the progress bar is activated and incremented.

Example of how to use the VBA Progress Bar in Excel:

Sub TestProgressBar()
    'Activate the progress bar - switch to the progress bar worksheet
    Call ActivateProgressBar(ActiveWorkbook, ActiveSheet, 100)
    For i = 1 To 100
        Call AddOneProgress
        DoEvents 
    Next i
    'Deactivate the progress bar - switch to your original worksheet
    Call DeactivateProgressBar(ActiveWorkbook)
End Sub

So simple! Notice also when opening the example file that the update procedure contains the “DoEvents” command. This guarantees that when doing long computations the Excel screen will not freeze but will update and be responsive. The “DoEvents” command is better than using a “Sleep” command as the “Sleep” command does not always work as expected (sometimes freezing Excel) and will unnecessarily extend the time needed to execute the macro. Whereas the “DoEvents” command will only carry out pending Excel events (usually just refreshing the window).