• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 55
  • Last Modified:

Creating a timer in excel for runners in 5km race

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.
Todd Dubber
Todd Dubber
  • 2
3 Solutions
crystal (strive4peace) - Microsoft MVP, AccessRemote Training and ProgrammingCommented:
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.

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
Look at sample
Solution was given
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now