Tag Archives: alarm

VBA Timer: Create a Stopwatch in Excel VBA

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

In this post we will explore the various ways we can set a Timer in Excel VBA as well as how to build your own VBA Stopwatch. Setting timers can be extremly useful if you want to run code as specific time intervals. On the otherhand a practical example can be a VBA Stopwatch which can be useful to measure time elapsed directly in a Excel spreadsheet.

VBA Timer

The most simple example of a VBA Timer can be made using the VBA Timer function:

VBA Timer with Hours, Minutes and Seconds

The above is very useful if you want to measure time elapsed in Seconds, if you want to calculate time elapsed in Hours, Minutes and Seconds you can simply use the VBA Now function with text formatting using VBA Format instead:

VBA Wait

In case you don’t want to measure time elapsed and instead set an interval or delay between code execution you either use the VBA Sleep or VBA Wait procedures like below. Remember that the VBA Sleep function is not a native VBA function and needs to be declared.

VBA Alarms and Scheduling

Another VBA Timer scenario is scheduling procedures to run at a specific time or after a specific time interval like an alarm clock. To do this we use the Excel VBA OnTime procedure:

You can use the VBA OnTime function also to schedule code execution after a specific duration of time. The below will save the current file after every 5 min.

VBA Stopwatch

Using the VBA OnTime function we can also create a simple Excel VBA Stopwatch:
VBA Stopwatch in Excel
As you can see in the example above I created a button that launches a Start / Stop sequence. When the stopwatch is running it increments the TIMESTAMP cell (a named cell). You can use the code below to achieve this. Remember to select your named range as well as to connect your button to the StartStop procedure.

What happens above? When you hit the button the AddSecond procedure will be set to run within a second and then automatically sets itself to run in the next second after updating the timestamp. The next hit of the button will turn of the schedule. This approach may see some slight delays over a longer duration of time (as we are running code before the schedule), however, it should be good enough.

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

1 Star2 Stars3 Stars4 Stars5 Stars (3 votes, average: 4.67 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