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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

excel vba timer event

Read More: Excel VBA to Create Timer Loop


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.

Read More: How to Create a Countdown Timer in Excel VBA


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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo