How to Insert a Timer in MS Excel

According to "Special Edition: Using Microsoft Office 2007," Visual Basic for Applications (VBA) is the underlying programming language running macros in Office 2007 and earlier versions of Microsoft Office programs. Since the timer function is included in VBA, it is possible to insert this tool in programs such as Excel. After creating the code, it can be run through the macro functions or with a shortcut key. A typical use of the timer is to set reminders for specific tasks, while working in a program like Excel.

Difficulty:

Moderately Easy

Instructions

1. Open an Excel spreadsheet and click on "Developer" in the menu bar at the top of the Excel screen. If you do not see Developer listed, then click the "Microsoft Office" button, "Excel options," "Popular" and select the "Show Developer" tab.

2. Click "Insert" in the new window that appears and choose "Procedure" from the drop-down menu. Give the new procedure a name like "ExcelTimer" and ensure the type selected is "Sub."

3. Type in the following text on separate lines in the right-hand pane between the "Public Sub ExcelTimer()" and "End Sub," which will already be there:
   Dim PauseTime, Start, Finish, TotalTime
   If (MsgBox("Press Yes to Get a Break Reminder", 4)) = vbYes Then
   PauseTime = 10
   Start = Timer
   Do While Timer < Start + PauseTime
   DoEvents
   Application.Speech.Speak "Time to take a Break!"
   Loop
   Finish = Timer
   TotalTime = Finish - Start
   MsgBox "Break lasted for " & TotalTime & " seconds"
   Else
   End
   End If
   End Sub

4. Check the function by pressing the "F5" key. A standard Excel spreadsheet should open, and you should see a small window with the words "Press yes to get a break reminder": Click on the "Yes" button and watch what happens. If you don't see this little window, then check to make sure you have inserted the code in Step 3 correctly.

5. Click on "OK" when the timer finishes running to return to the spreadsheet.

6. Assign a shortcut key to this action by highlighting it in the macro lists and clicking on "Options."

Tips & Warnings

• The duration of the timer can be changed to whatever you want in the "Pause time" section. The "DoEvents" command in the code will raise a simple speech application. This can be changed to whatever action you would like to have timed.

• This particular example will only be heard if you have a speaker. Other events that do not require sound can be substituted.

References

• Special Edition: Using Microsoft Office 2007, Ed Bott and Woody Leonhard, 2007.

Resources

• Read this Article in Spanish
• Visual Basic for Applications Samples