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

countdown timer in excel vba
Get FREE Advanced Excel Exercises with Solutions!

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.

Create a Basic Interface for the Countdown Timer

Read More:How to Create a Timer with Milliseconds in Excel VBA


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.

Incorporate VBA Code countdown timer in excel vba

  • 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

Incorporate VBA Code countdown timer in excel vba
Now, we will explain how the given VBA code works.

Code Breakdown

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.

Set the Timer Button countdown timer in excel vba

  • 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.

Set the Timer Button

  • Now the interface will look like below.

Set the Timer Button

  • 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?

Test the Countdown Timer countdown timer in excel vba

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.


Conclusion

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.

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:

Mohammad Shah Miran
Mohammad Shah Miran

Mohammad Shah Miran has a professional background spanning over a year at Softeko. Initially starting as an Excel and VBA writer, he authored more than 50 articles for the ExcelDemy project. Currently, Miran is engaged in the LinuxSimply project as a Linux content developer, completed over 40 articles. His analytical approach extends across various domains, including Excel, VBA, Bash scripting, Linux, data analysis, and Python programming. In his leisure time, Miran enjoys watching movies and series or listening... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo