Imagine you want to countdown your time while you are doing a specific task. As we all know, you can do it by using your wristwatch. But what if you don’t have any? Well, you can create a Countdown timer in Excel VBA on your own. Follow the discussion below to learn more about it.
What Is a Countdown Timer?
A countdown timer is a form of a virtual clock that counts down from a certain date or number to mark the beginning or end of an offer or event. The main purpose of a countdown timer is to create a sense of urgency and the idea that “You are running out of your time”
Businesses also use countdown timers to show how long it will be before a particular pricing or offer becomes available.
4 Steps to Create a Countdown Timer in Excel VBA
Our today’s topic consists of four easy steps. The very first thing you need to do is create an interface to proceed with your work. Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.
Step 1: Create a Basic Interface for the Countdown Timer
To make our countdown timer visually appealing, apart from navigating the clock, we need to create a basic interface in this regard. Initially, we have to prepare an outline, as shown in the below image.
Step 2: Incorporate VBA Code
Thanks to VBA, users have access to features that go beyond what the MS Office software suite provides. By adding user defined functions, automating business-specific tasks, or adding a unique feature, users can further alter applications to suit their particular needs. However, here we will incorporate VBA code to create a countdown timer. To do so,
- Press Alt + F11 to open your Microsoft Visual Basic.
- Then press Insert > Module to open a blank module.
- Now, write the following VBA code in your Module1.
Option Explicit Public Clock As Date Sub StartClock() Clock = Now + TimeValue("00:00:01") If Range("B4").Value = 0 Then Exit Sub Range("B4") = Range("B4") - TimeValue("00:00:01") Application.OnTime Clock, "StartClock" End Sub Sub ResetClock() Range("B4") = TimeValue("00:00:15") End Sub Sub StopClock() Application.OnTime EarliestTime:=Clock, Procedure:="StartClock", Schedule:=False End Sub
Now, we will explain how the given VBA code works.
The code is divided into three parts.
- In the first portion, starting with sub StartClock to initiate the countdown. Then we assign VBA Now function so that it can return the time and date as they are on our computer. In addition to that, we also call VBA TimeValue function to return the string “00:00:01” as a time. Then those two time values make a sum and return its data in the Clock Then the loop which has been assigned in 5th and 6th line will return data on the basis of data in the B4 cell. However, we call Application.onTime later to run the timer until the value of B4 meets. We specify the B4 data in the second portion of the VBA code. In the 7th line of the code, as Range(“B4”) data is subtracted by 00:00:01, the clock will count the digit in the reverse direction and will end up at 00:00:00.
- The second portion of our code has been assigned to allow the countdown timer to have a reset button so that we can reset our timer if needed. It will reset the clock at 00:00:15.
- In the third portion of our VBA code, we set another sub-function named StopClock which we will plug with another button named Stop to stop our timer.
- Press F5 to run your VBA Code.
- Close your VBA window.
Read More: Excel VBA to Create Timer Loop
Step 3: Set Timer Button
So, our VBA code has been integrated. Now it’s time to add some timer buttons to operate our countdown clock. Here we will add three buttons named Start, Stop, and Reset.
- Closely look at the GIF (Graphics Interchange Format) that we have attached below for your understanding.
- Here first go to Developer > Insert > Tab.
- Then select the region where you wish to place your button.
- Subsequently a dialog box will pop up.
- Select StartClock from Assign Macro
- Press OK afterwards.
- Follow the same process to add the Stop. But this time, remember to select the StopClock option from the Assign Macro dialog box.
- Press OK later.
- Now the interface will look like below.
- Follow the same process to add the Reset Select the ResetClock option from the Assign Macro dialog box.
- Press OK afterwards.
- Thus, the final output will be as below.
Read More: How to Delay Timer with VBA in Excel
Step 4: Test Countdown Timer
We have made sufficient progress to run our clock. As you have already experienced how to incorporate basic interface, VBA code, and creation of buttons. Now it’s time to play with our toy. See the GIF we have attached below.
- Click on the buttons to see the subsequent response. Looks fun, doesn’t it?
Read More: How to Create Timer Event with VBA in Excel
Things to Remember
- It is to be mentioned that once you assign the button to your Excel sheet, you won’t allow any modification of your VBA If you do, you have to set your clock buttons (Start, Stop or Reset) again. The code will not run otherwise.
Download Practice Workbook
You can download and practice the dataset that we have used to prepare this article.
In this article, we have discussed how to create a countdown timer in Excel VBA. We hope you have learned that perfectly. If you have any queries, feel free to comment below and we will get back to you soon.