?
Solved

Creating a timer in excel for runners in 5km race

Posted on 2016-10-16
4
Medium Priority
?
40 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 22

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
   
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 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
0
 
LVL 40

Accepted Solution

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

Expert Comment

by:als315
ID: 41875266
Solution was given
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

770 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