How to Create Timer Event with VBA in Excel (with Easy Steps)

Published by Johnny Goudie on

A timer in Excel is used for counting down time. For practical purposes you may need to create this timer event option in Excel. There are several ways for preparing a timer event in Excel. If you want to create that with the application of VBA code, then you have come to the right place. In this article, we are going to show you how to create a timer event in Excel using simple VBA code.


What Is Timer Event in Excel?

Timer event is a setup in Excel that allows the user to track time. With this setup we can calculate the duration of elapsed time and also can keep records of the start and stop time.

Timer event is particularly important for finding time intervals between two events.


Download Practice Workbook

You can download and practice this workbook from the link below.

Creating Timer Event.xlsm

Stepwise Demonstration of Creating Timer Event with VBA in Excel

In this section, we will show the step by step procedure to create a timer event in Excel. For that we will use VBA code. I will discuss the steps of creating the timer event by using the Excel VBA with proper illustrations. So let’s begin the procedure.


Step 1: Create Suitable Data Table

First of all, make a format for creating the timer event. For the timer event we need three columns for the results. That’s why we have created this data table where we can show the Start time, Stop time and Duration time.


Step 2: Inserting VBA Code to Create Timer Event

We are using simple VBA code for this problem. In order to create a timer event, we have to insert a suitable VBA code. And for this:

➤ Go to the Developer tab and then click on the Visual Basic option under the Code group on the Excel toolbar.

As a result, a new window with header “Microsoft Visual Basic for Applications” will appear on the screen instead of the home window.

➤ Next, click the Insert tab and select Module from the menulist.

➤ Now, write the corresponding VBA code. You can use the following.

Code:

Sub StartColumn()
Range("C4:C13").Find("").Select
ActiveCell.Value = Time
Selection.NumberFormat = "hh:mm:ss"
End Sub

Sub StopColumn()
Range("D4:D13").Find("").Select
ActiveCell.Value = Time
Selection.NumberFormat = "hh:mm:ss"
Range("E4:E13").Find("").Select
ActiveCell.Value = Format(Time, "Long Time")
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-2]"
Selection.NumberFormat = "mm:ss"
End Sub

excel vba timer event

Next, you need to save the code. You can’t save this in a macro free workbook. You have to enable the macro for the workbook. When you click to save the file, a pop up will appear asking whether you want to save it in a macro-free workbook. Select No and enable macro for the workbook and save the file.


Step 3: Create Start and Stop Button

We have to create Start and Stop button to give proper command.

➤ Go to Insert > Illustration > Shapes and select the rectangular box.

➤ Now, with the mouse cursor, create two buttons and name them as Start and Stop.

You can locate those buttons in a suitable position and give them a nice color.


Step 4: Assign Macro in Start and Stop Button

Assigning Macro for Start Button

➤ Place the mouse pointer on the Start button and press Right.

➤ Then, click on Assign Macro in the appearing window.

excel vba timer event

➤ And, Select StartColumn in the Macro name box.

➤ Make sure you set This Workbook in the Macros in box.

➤ Then press OK.

excel vba timer event

Assigning Macro for Stop Button

Similarly,

➤ Place the mouse pointer on the Stop button and press Right.

➤ Then, click on Assign Macro in the appearing window.

➤ And, Select StartColumn in the Macro name box.

➤ Make sure you set This Workbook in the Macros in box.

➤ Then press OK.

excel vba timer event


Step 5: Execute the Timer Event in Excel

Let’s see how this setup works.

➤ Click on the Start button.

The start time will appear in the first cell of the Start column.

After some time,

➤ Click on the Stop button.

So, the Stop time and the Duration time will appear in their respective cells.

excel vba timer event

Similarly, you can execute this process again to get more set of timer events.

Note: Make sure to click the Start button before the Stop button.


Practice Section

We are providing the Excel workbook that you can download and practice yourself.

excel vba timer event


Conclusion

Thank you for making it this far. We have shown you how to create timer event in Excel using VBA code. Hope, you find the content of this article useful. If you have any queries or suggestions please do let us know in the comment section.

Categories: TECH