In a previous post, I talked about creating an alarm in Excel, which you can use to post an alert at a specific time. Continuing in the same vein, today's topic is creating a stopwatch in Excel.
There are many reason you might want a stopwatch, and of course, just as many apps on the market that provide one. But let's assume in this case, it's convenient for you to have a stopwatch embedded directly in your Excel worksheet.
The Application
For purposes of this exercise, let's assume that you've got a time-tracking worksheet where you log the amount of time you spend on the phone with your clients or customers. It might look like this:
As you can see, I've got one entry in the file so far, for 2 hours and 10 minutes of billable time. This app assumes that you bill in hours and minutes, but could easily be adapted to other units. Maybe you want to know how many minutes/seconds it takes to run a specific update process, or maybe you're timing how many seconds it takes you to run one lap around your office - I don't know, and I don't need to know - more power to you, whatever it is.
I've also got a "Start Timer" button on the worksheet. We'll use that button to track the time we spend with the next customer who calls. Let's say that Jane Smith has just called, and I'm about to start billing for her time. I'll go ahead and enter her name in cell A3, and today's date in B3 (hint: Use Ctrl-semicolon to quickly add today's date).
Next I'll start the stopwatch, to record the exact amount of time I'm spending with Jane. When I click the button, three things will happen:
I'll put the current time in column C of the active row.
I'll start the stopwatch
I'll set a timer to update the value in column E every second
I'll change the text on the button to read "Stop Timer", instead of "Start Timer"
Before I get to the code, let's talk about some of the "whys".
Why "active row"? I'm going to set up this code to act on whichever row is currently active. Right now, I'm working on row 3. But obviously, I'll want it to work on row 4 for the next caller, and row 5 after that. For simplicity, I'm going to assume that my user can be counted on to always leave the cursor on the most recent row. This makes my code easy.
Why the timer? I'm going to want to update column E frequently, so that the user can easily see the elapsed time. I'll set it to run every second, but you could set it for every minute or every 30 seconds - whatever makes sense for your application.
Why change the text on the button? I'm going to use that button as a toggle. You'll click it once to start the timer, and then again to stop the timer. You could use two buttons instead, but then you'd have to worry about disabling one, or having some error code to prevent the user from clicking Start more than once before clicking Stop. I'm going to take the easy way out, and use a single button.
OK, on to the code.
First I'll show you the code, then I'll break it down to explain how it works:
Starting at the top, let's look at the variable declarations.
Variables
The "ElapsedTime" variable is my stopwatch, where I keep track of how much time has passed since you started the stopwatch. Notice that I've declared it as Date. Despite that, I will never specify a date for this variable. I'm strictly using it to capture the number of hours, minutes and seconds since you clicked the "Start Timer" button, so the date portion is irrelevant. Every "date" variable includes both a date and a time. I'm only going to use the time portion.
The "StartTime" variable is used to kick off my timer on a regular interval, in this case, every second. The start time will always be one second later than the current time. In this case, I do use the date portion of the variable, because I always want the timer to run today, so actually, StartTime will be one second later than the current time on the current date.
The "rw" variable is used to determine which Excel row I'm working on. I've used a "Long" data type, so that this code works on all Excel rows. If I'd used an "Integer", it would only work on the first 32,767 rows.
Toggle Timer
The "ToggleTimer" procedure is the one that will run each time you click the "Start Timer" button.
I start by figuring out which row we're working on:
rw = ActiveCell.Row
I'll refer back to this every time I update the worksheet.
My "if" statement then checks to see if we're starting the timer or stopping it. Remember that I'm using a single button here, so I check the caption to find out which action to perform. In my case, I only have one button on my worksheet, so my code simply refers to the first button in the Buttons collection:
If ActiveSheet.Buttons(1).Caption = "Start Timer" Then
If I had more than one button on my worksheet, I'd need to take additional steps to pick out the one I'm referring to. But we're keeping it simple here.
Starting the Stopwatch
Now that I know I'm starting the timer, I perform several steps:
Set the initial ElapsedTime to 0, giving me a starting point.
Set the current time into column C of the worksheet, using the "Now" function, so that we know when the call started.
Set the StartTime to now. This will trigger our recurring code (which is the "UpdateStopwatch" code that we'll see in a minute), which should run immediately.
Set the "Billable Time" on the worksheet to 0 - once again, our starting point.
Kick off the timer. The "OnTime" statement tells Excel to run a specific bit of code at a specific time. In our case, it'll run the "UpdateStopwatch" code at the start time, which I've set to the current time. In other words, run it right now.
Finally, I change the text on the button to "Stop Timer".
Update Every Second
OK, so now our stopwatch is running. What I want to do next is to update the Billable Time value on the worksheet once every second, so that you can see how much time has elapsed in real time. That happens in the UpdateStopwatch procedure.
Remember that I kicked off this code with the "OnTime" statement when you clicked the "Start Timer" button.
Let's break down how UpdateStopwatch works.
First, I update the "StartTime" variable, adding 1 second to the current time. This will be used to re-run the UpdateStopwatch procedure 1 second from now.
Next, I update the "ElapsedTime" variable, adding 1 second, keeping track of how long the stopwatch has been running.
Then I copy that value onto the worksheet, so that the user can see the Billable Time.
I use the "OnTime" statement to kick off the next run of the "UpdateStopwatch" procedure.
Finally, I issue the DoEvents statement. This one is important. It tells Excel to finish up any activity that might be in Excel's queue before moving on. It's a little hard to explain why this is necessary, so you might want to read up on it. I think of it as telling Excel to take a second to catch its breath before moving on. If you omit this, you'll never get a chance to click the "Stop Timer" button, because Excel will always be busy processing commands that are in its queue.
Shutting it Down
The last step is to shut down the stopwatch when you click the "Stop Timer" button. This goes back to the "Else" statement in the ToggleTimer procedure. Here's what that one does:
Puts the current time in column D on the worksheet, so that the user can see when the call ended.
Kills the timer. Remember that the "UpdateStopwatch" procedure is currently running every second. This version of the "OnTime" statement shuts that down. Note the use of the "False" argument at the end of the command line - that's the one that tells Excel to stop the timer, so that UpdateStopwatch won't run again.
Update the "Billable Time" column on the worksheet with the final elapsed time
Reset the caption on the button to "Start Timer", so that its ready for its next use.
That's a Wrap
There you have it - a stopwatch that keeps track of elapsed time on your Excel worksheet. I used hours and minutes in my example, but you could, of course, use minutes and seconds, or even seconds and milliseconds.
I hope you find some useful application for this technique. As always, reach out to me here or via email if you have question about this code, or would like help with a similar application.
Kim
About the Author
Kimberlee Martin is the owner of North Port Solutions and has 30 years of IT experience. She's worked with several programming languages and database tools over the years, with her favorites being Microsoft Excel VBA, SQL Server, and Visual C#. Her passion is helping small businesses gain insights into their business with effective reporting and data management.
Contact Kim at kmartin@northportsoftware.com.
© North Port Solutions, LLC, 2023. All rights reserved.