Creating a timer in excel for runners in 5km race

Posted on 2016-10-16
Last Modified: 2016-11-05
I am looking to create a macro to group start a category of twenty runners and have the ability to stop each individual runners time when they finish.  I need help creating the vb code to put a macro into the "stop time" cells for each runner.
Question by:Todd Dubber
  • 2
LVL 19

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 167 total points (awarded by participants)
ID: 41845804
attached is an example workbook.

Column A lists the runner names
Column B is formatted as "styFinish"

Start the Race by clicking the Start Race button
The date/time will be put in F1 and the Timer value (elapsed seconds since midnight) in G1 so fractional seconds can be recorded.
Public giRunnerNumber As Integer

Sub StartRace()
'161016 crystal
   Dim sgStartTimer As Double
   sgStartTimer = Timer()
   giRunnerNumber = 0
   ActiveSheet.Range("F1") = Now()
   With ActiveSheet.Range("G1")
      .Value = sgStartTimer
      .NumberFormat = "0.00"
   End With
   MsgBox "Race has started!", , "Start date/time: " & Now()
End Sub

Open in new window

Excel - click button to start raceAs runners come in, click Column B next to their name. the runner number will be filled in column B, elapsed hours in C, minutes in D, and seconds in E.  The worksheet selection change event is used to look at the cell you clicked in.  If it is formatted as "styFinish then code runs.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'161016 crystal
   On Error GoTo Proc_Err
   If Target.Style <> "styFinish" Then Exit Sub
   Dim nRow As Long _
      , dbStartTimer As Double _
      , dbSeconds As Double _
      , dbSecondsLeft As Double _
      , iHours As Integer _
      , iMinutes As Integer
   nRow = Target.Row
   dbStartTimer = ActiveSheet.Range("G1")
   giRunnerNumber = giRunnerNumber + 1
   dbSeconds = Timer() - Range("G1")
   iHours = dbSeconds \ (60 * 60)
   dbSecondsLeft = dbSeconds - (iHours * 60 * 60)
   iMinutes = dbSecondsLeft \ 60
   dbSeconds = dbSecondsLeft - (iMinutes * 60)
   Cells(nRow, 2) = giRunnerNumber
   Cells(nRow, 3) = iHours
   Cells(nRow, 4) = iMinutes
   With Cells(nRow, 5)
      .Value = dbSeconds
      .NumberFormat = "0.00"
   End With
   On Error Resume Next
   Exit Sub
   Resume Proc_Exit
End Sub

Open in new window

Excel - as runners fin ish the race, their time is recordedNote: a global variable was used for the runner number.  you may want to also keep track of this in a cell since global variables can be lost.

LVL 31

Assisted Solution

Frosty555 earned 167 total points (awarded by participants)
ID: 41845807
Doesn't sound too hard to do.

I think rather than using a timer you should just record the date/time that you clicked various cells and use a formula to calculate the difference between them.

Hook into the Worksheet.SelectionChange() event. That event will trigger whenever you click on a cell, so you can write code that changes the value of that cell to be the current time. You can check which cell is being clicked on and perform different actions depending. YOu can use the GetTickCount() API for higher precision if necessary.

So for example, you have a worksheet with all your runners listed with their names in column A and the Finish Time in column B. Have B1 be the "start" of the race and when you click that cell it writes the start time. When you click any other cell B2 through B99 it writes it writes the datetime stamp into the cell, signifying the "end" of the race for that runner.

Then you can just make Column C be a formula that subtracts the two datestamps and shows the duration in seconds.

For the column C formula you could set it to, for example "=(B2-$B$1) / 1000" and then use the little black square in the bottom right to extend that forumla to all the other cells. the $B$1 is an absolute reference so it won't change while the B2 will become B3, B4, B5 etc.

And the macro code would look something like this:

Private Declare Function GetTickCount Lib "kernel32" () As Long
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Column = 2 Then
        Target.Value = CStr(GetTickCount)
    End If
End Sub
LVL 39

Accepted Solution

als315 earned 166 total points (awarded by participants)
ID: 41845834
Look at sample
LVL 39

Expert Comment

ID: 41875266
Solution was given

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question