Excel macro to count time
19th Mar 2013 | 06:18
Learn how to use Excel's built-in timer
When you're busy working in Excel and you need to remember to do something else later on, you can set a reminder for yourself using a macro.
This Excel macro does just this: it asks you for a reminder message and when it should show you that message. It then lets you work while it keeps time in the background.
When the time has expired, it will show you the reminder message. In creating this macro you'll learn to use the OnTime method to run procedures at a future time.
The step-by-step guide below shows you how to create the reminder macro as well as how to test it. It's contained in two subroutines, one of which sets up the alarm and the other simply displays the message. The OnTime method is used to count the time in the background so you can do other things in the meantime.
You'll find the macro in the file reminder.xlsm via TechLife Macro download.
You'll also see that there are instructions in the file for running the macro and for moving it to your personal macro workbook so you can use it anytime you're working in Excel.
Once you've moved it, you can add the macro to your Quick Access Toolbar or the Ribbon.
Step by step: How the timer macro works
Start in Excel by clicking the 'Developer tab > Visual Basic > Insert > Module' to add a new module. Type the procedure to display the message when the alarm goes off.
The timer code is a separate procedure. It gets the time and a message from the user and then activates a timer that counts down. When the time expires, it runs the previous procedure.
Test the macro by running it, setting a time and waiting. When you're done, provided you've added it to your personal workbook, you can add a button to your QAT or Ribbon to automatically run it anytime.