How To Create Timer In Excel

While working at IBM, the author was a member of the ToastMasters International Club. It's where you can improve your communication and leadership skills.

 

An interesting part of weekly meetings is spontaneous talk. During this part of the meeting, a person was given a topic and he / she had to talk about it for 2 minutes. There was a designated person who had timed to speak and showed up for a green card at 1 minute, a yellow card at 1:30 and a red card after two minutes.

Usually, the cell phone or watch used to time the speech and the execution time is manually recorded on paper. It often happens that the person forgets to show color cards or sometimes forgets to jot down the timing for the speakers (which happens to me all the time). With these in mind, I have created a stopwatch in Excel that can help time and record speeches.

Let's first learn how to create a basic stopwatch in Excel.

How to create a Stopwatch in Excel (Basic)

https://www.youtube.com/watch?v=sbJeGG_Xv8M

By a simple / basic stopwatch in Excel, I mean something that starts when we press the start button and stops when we press the stop button.

Something like is shown below:
To create this stopwatch in Excel, you need to know about Apps. Onime method in VBA.

Application.OnTime Method in VBA

The Application.OnTime method can be used when you want to run a specified code in the future. For example, you can use it to display a message box reminding you to get up and stretch your legs after 1 hour, or take a pill after 3 hours.

Application Syntax. Mode of Time:

Application. OnTime (EarliestTime, Procedure, LatestTime, Schedule)

• The earliest time: The time when you want to run the procedure.
• Procedure: The name of the procedure should be run.
• RecentTime (Optional): In case another code is running and your specified code cannot run at the specified time, you can specify RecentTime which it should wait for. For example, it could be at the earliest + 45 (meaning it will wait 45 seconds for another procedure to complete). If even after 45 seconds the process cannot run, it is canceled. If you don't specify this, Excel will wait until the code can be run and then run it.
• Schedule (Optional): If set to True, it will schedule a new timeline procedure. If Wrong, cancel the procedure previously booked. By default, this is true.

An example of an application:

Sub test()
Application.OnTime Now + TimeValue("00:00:05"), "ShowMessage"
End Sub

Sub ShowMessage()
MsgBox ("HELLO")
End Sub

The first part of the macro uses the Application.OnTime method and runs the ShowMessage procedure (in quotation marks) after five seconds. The ShowMessage procedure simply displays the message box with the HELLO prompt.

You can use this format to run any procedure after a specified time from the present point in time. Now using this concept, let's see the code for creating a simple stopwatch in Excel.

Dim NextTick As Date, t As Date
Sub StartStopWatch()
t = Time
Call StartTimer
End Sub

Sub StartTimer()
NextTick = Time + TimeValue("00:00:01")
Range("A1").Value = Format(NextTick - t - TimeValue("00:00:01"), "hh:mm:ss")
Application.OnTime NextTick, "StartTimer"
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=NextTick, Procedure:="StartTimer", Schedule:=False
End Sub

This code has 3 parts:

The first part initializes the current time for the variable t. It then calls another procedure StartTimer.

The StartTimer procedure uses the NextTick variable which is incremented by 1 with each elapsed second. In the worksheet, cell A1 has a run timer as we have specified NextTick - t -TimeValue ("00:00:01"), "hh: mm: ss") is the value in that cell. It will then re-run the StartTimer code every second.

StopTimer cancels the Application.Ontime procedure by setting a false schedule value. This stops the timer.

Here's what you will get with the above code (I assigned macros to the start / stop buttons)

This is a basic stopwatch in Excel.

I call it basic because you can't stop in the middle and restart where you left off. It will always reboot from 1 when you press the start button.

Now that you've learned the basics of the Application.OnTime method, you can easily adjust this to create any type of stopwatch you want in Excel.

Stopwatch in Excel (For ToastMasters)

I used the concept discussed above and created a Stopwatch in Excel that can be used in a Toastmasters meeting (which I mentioned at the beginning of this tutorial).

Here are the things that can be done with this stopwatch:

• You can stop timer and then restart word at the same time (recorded until then).
• You can reset the timer. This sets the timer value to 0. As soon as you do it, it will automatically record the total elapsed time.

It changes the color of the timer box, depending on the stopwatch value (this can be a good reminder to show green / yellow / red cards).

Here's how it looks:

In the demo above, I have set up color changes every five seconds. You can easily specify when you want to change the color (green card for 1 minute, yellow card after 1.5 minutes, and red card after 2 minutes) by changing the values ​​in the Calculation panel.

As soon as you press the reset button, the timer color will return to white, the timer value will become 0, and it will record the time in column G.

Note: Since these files contain macros, you will have to enable them before you can use them. When you open the workbook, you will see a yellow button - Enable Content. Click it to activate the macro.

If you create something cool using a timer, share it with me.

Soure: Blebees.com

source https://blebees.com/how-to-create-timer-in-excel/