Creating a timer in excel for runners in 5km race

Posted on 2016-10-16
Medium Priority
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 23

Assisted Solution

by:crystal (strive4peace) - Microsoft MVP, Access
crystal (strive4peace) - Microsoft MVP, Access earned 668 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 668 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 40

Accepted Solution

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

Expert Comment

ID: 41875266
Solution was given

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

In this post, I will showcase the steps for how to create groups in Office 365. Office 365 groups allow for ease of flexibility and collaboration between staff members.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

809 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