Solved

Creating a timer in excel for runners in 5km race

Posted on 2016-10-16
4
28 Views
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.
0
Comment
Question by:Todd Dubber
  • 2
4 Comments
 
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
   
Proc_Exit:
   On Error Resume Next
   Exit Sub
  
Proc_Err:
   Resume Proc_Exit
   Resume
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.

Example:
solution_TimeRunnersInRace_crystal_.xlsm
0
 
LVL 31

Assisted Solution

by:Frosty555
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
0
 
LVL 39

Accepted Solution

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

Expert Comment

by:als315
ID: 41875266
Solution was given
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Using Word 2013, I was experiencing some incredible lag when typing.  Here's what worked for me....
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now