An interesting section in the weekly meetings was impromptu speaking. In this part of the meeting, a person was given a topic and he/she had to speak on it for 2 minutes. There was a designated person who used to time the speech and shows a green card at 1 minute, a yellow card at 1:30 minutes, and a red card after 2 minutes. Usually, a smartphone or a watch is used to time the speech, and the time taken is recorded manually on a paper. It often happens that the person forgets to show the colored cards or sometimes forgets to record the timing for the speakers (happened with me all the time). With these things in mind, I have created a stopwatch in Excel that would help time and record the speeches. Let’s first learn how to create a basic stopwatch in Excel.
How to Create a Stopwatch in Excel (Basic)
By a simple/basic stopwatch in Excel, I mean something that would start when we press the start button and stop when we press the stop button. Something as shown below:
Download this Basic Stopwatch in Excel To create this stopwatch in Excel, you need to know about the Application.Ontime method in VBA.
Application.OnTime Method in VBA
Application.OnTime method can be used when you want to run a specified code in the future. For example, you may use it to show a message box to remind you to get up and stretch your legs after 1 hour or have medicines after 3 hours.
Syntax of Application.OnTime Method:
Application.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
EarliestTime: The time when you want to run the procedure. Procedure: The name of the procedure that should be run. LatestTime (Optional): In case another code is running and your specified code can’t be run at the specified time, you can specify the LatestTime for which it should wait. For example, it could be EarliestTime + 45 (which means it will wait for 45 seconds for the other procedure to get completed). If even after 45 seconds the procedure it not able to run, it gets abandoned. If you don’t specify this, Excel would wait until the code can be run, and then run it. Schedule (Optional): If set to True, it schedules new time procedure. If False, then it cancels the previously set procedure. By default, this is True.
An Example of Application.OnTime Method
The first part of the macro uses the Application.OnTime method and runs the procedure ShowMessage (in double quotes) after five seconds. The ShowMessage procedure simply shows the message box with the prompt HELLO. You can use this format to run any procedure after a specified time from the current time. Now using this concept, let’s look at the code for creating a simple stopwatch in Excel. This code has 3 parts:
The first part initializes the current time to the variable t. Then it calls another procedure StartTimer. StartTimer procedure uses a variable NextTick which gets incremented by 1 with every passing second. In the worksheet, cell A1 has the running timer as we have specified NextTick – t -TimeValue(“00:00:01”), “hh:mm:ss”) to be the value in that cell. It then runs the StartTimer code again after every second. StopTimer cancels the Application.Ontime procedure by making the schedule value False. This stops the timer.
Here is what you’ll get with the above code (I have assigned the macros to the start/stop buttons):
This is a basic stopwatch in Excel. I call it basic as you can not stop in middle and restart where you left. It will always restart from 1 when you press the start button. Now that you have learned the basics of Application.OnTime method, you can easily tweak this to create whatever kind of stopwatch you want in Excel.
Stopwatch in Excel (For ToastMasters)
I have used the concept discussed above and created a Stopwatch in Excel that can be used in the Toastmasters meeting (which I mentioned at the beginning of this tutorial). Here are the things that can be done using this stopwatch:
You can stop the timer and then restart again from the same time (recorded till then). You can reset the timer. This sets the timer value to 0. As soon as you do that it automatically records the total time elapsed. It changes the color of the timer box, depending on stopwatch value (this could be a good reminder to show the green/yellow/red cards).
Here is how it looks:
In this above demo, I have set the color change every five seconds. You can easily specify when you want the color to change (the green card at 1 min, yellow card at 1.5 minutes, and a red card at 2 minutes) by changing the values in the Calculation sheet. As soon as you hit the reset button, the color of the timer would go back to white, the value of the timer would become 0, and it will record the time in column G. Download the ToastMasters Style Stopwatch in Excel Note: Since these files contain macro, you will have to enable macros before using it. When you open the workbook, you’ll see a yellow button – Enable Content. Click on it to enable macros.
If you create something cool using a timer, do share it with me.
Matrix Falling Numbers Effect in Excel using VBA. How to Automatically Insert Date and Time Stamp in Excel. Using Loops in Excel VBA. Free Excel Templates. Creating a Dashboard in Excel.
Option Explicit Dim NextTick As Date, t As Date, PreviousTimerValue As Date Private Sub StartTime() PreviousTimerValue = Calculations.Range(“A1”).Value t = Time Call ExcelStopWatch End Sub Private Sub ExcelStopWatch() Calculations.Range(“A1”).Value = Format(Time – t + PreviousTimerValue, “hh:mm:ss”) NextTick = Now + TimeValue(“00:00:01”) If Calculations.Range(“A1”).Value > Calculations.Range(“B3”) Then With Sheet4.Shapes(“TimeBox”) .Fill.ForeColor.RGB = RGB(0, 255, 0) End With End If Application.OnTime NextTick, “ExcelStopWatch” End Sub Sub StopClock() On Error Resume Next Application.OnTime earliesttime:=NextTick, procedure:=”ExcelStopWatch”, schedule:=False With Sheet4.Shapes(“TimeBox”) .Fill.ForeColor.RGB = RGB(255, 0, 0) End With End Sub ‘If Calculations.Range(“A1”).Value > Calculations.Range(“B3”) Then ‘ With Sheet4.Shapes(“TimeBox”) ‘ .Fill.ForeColor.RGB = RGB(0, 255, 0) ‘ End With ‘End If I’ve downloaded theToastMaster Stopwatch. I want to make it recording without clicking the reset button by using spesific criteria, for example Stopwatch.Range(“I11”).Value = 1. I’m not good at VBA. So sorry for this silly question. Hope you can help me here. I’m looking forward your reply. Thank you. I’ve downloaded theToastMaster Stopwatch. I want to make it autorecording without clicking the reset button by using spesific criteria, for example Stopwatch.Range(“I11”).Value = 1. I’m not good in VBA. So sorry for this silly question. Hope you can help me here. I’m looking forward your reply. Thank you.
