Tag Archives: progress bar

vba alerts and notifications

VBA Status Bar, Progress Bar, Sounds and Emails – Alerts in VBA

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

Some VBA Macros run for ages – be it minutes or even hours. If we optimized our VBA Macro to the fullest there is often not much more you can do to change that. Usually what is irritating is waiting for VBA Macro code completion. Sitting by your desk is a waste of time – why not tell Excel/Access to let you know when the code has completed? Today we will explore such tools as the VBA Status Bar, VBA Progress Bar, Sound Notifications in VBA and Sending Email Alerts from VBA.

VBA Progress Bar

vba progress barProgress Bars are ubiquitous anywhere anything takes more than a couple of seconds to complete. So why doesn’t VBA have a native Progress Bar – beats me.

To add a VBA Progress Bar to your Visual Basic for Applications macro read my post here.

Excel Status Bar

vba status barThe VBA Status Bar is a panel that appears at the bottom of your Excel (or Access) Workbook. It is basically a Text Box to which you can display any non-blocking (non-modal) Message to your users.

Showing a Message in the VBA Status Bar

To show a message in the VBA Status Bar we need to Enable it using Application.DisplayStatusBar:

Hiding the VBA Status Bar

The VBA Status Bar when displayed, will remain displayed until ordered otherwise. It is best to hide and clean it after code execution completion:

Sound Notifications in VBA

What if you are away from your Screen, getting a Coffee or simply chatting with a colleague or getting on with our tasks stressed that we might miss that moment when our VBA Macro completes its task. Well why not introduce a Sound Notification? Let Excel or Access run a Sound to inform you that your Visual Basic for Applications macro has finally completed.

Playing a Sound in VBA

Playing a Sound in VBA is easy. Just add the below code snippet to a VBA Module:

It will play the Chimes.wav sound Once.

What if you don’t hear it? Running it once might obviously be not enough…

VBA Sound Alarm

The best approach is to create a VBA Sound Alarm that will run in a loop until we Turn it Off manually. What we will need is a Non-Modal UserForm as a pop-up to ask to stop the alarm.

First the macro code:

What happens here?:

  • I am measuring the execution time with the startTime and execTime variables
  • I am using the stopPlaying global VBA Variable to Turn Off the Alarm from our Alarm UserForm changes this value to True
  • I am displaying the AlarmForm with the Turn Off Alarm button

And now our AlarmForm code:

The result:
turn off vba sound notification

VBA Send Email Notification

Lastly when we are on the run and leaving our Workstation to process our VBA Macro remotely we might appreciate an email from Excel saying: Hey there! I just completed running the Macro!

You can find the code for the SendEmailFromOutlook function in my post here.

We might want to spice things up with maybe sending also the time it took to complete the macro:

Cool right? Makes the VBA Message Box hide in shame doesn’t it? The email should look like this:

vba email notification
VBA Email Notification

vba progress bar

Animated VBA progress bar for Excel and Access

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 5.00 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).