Implementing a Stopwatch in Excel...

[traversd]

Can anyone help me with a little project I've dreamed up ?

I need to be able to make an accurate stopwatch in Excel, I want to be able to time a load of Ice Hockey players doing sprints using a laptop on the ice. Excel is perfect cos I can then have a list of players, and just click the button next to the name for them to start..then stop the time. and the Dt is there loaded.

My ultimate goal would be to then modify the code and have have a hardware circuit connected to the parallel port so that the timing could be donw with an external circuit triggering the stopwatch. I'm lookinf for something accurate to hundredths of a second.

Is this possible with Excel and VBA ? Can anyone help start me off ?

Cheers
Darren

 

[rowett]

As a start take a look at the Timer function. It returns the number of seconds as a Single.

As an example I created two buttons StartButton and StopButton. The code is as follows:

Option Explicit

Private startTime As Single

Private Sub StartButton_Click()
    startTime = Timer
End Sub

Private Sub StopButton_Click()
    Dim finalTime As Single
    finalTime = Timer - startTime
    MsgBox Format(finalTime, "0.00")
End Sub

Regards,
Chris

 

[traversd]

Chris,

Thanks for that...a great starting point. Hadn't realised you could implement something as good in such a few lines !

 

[harry_thorne]

similar project:
how did you get on with your project? did you manage to use the paralell port for a light sensor (i presume)?
what I would like to do is to time runners around a running course. the difficulties arise because they set off one at a time in the particlular events i do. they also finish in a different order. What i thought might work would be to have, firstly as lidt of competitor numbers that is your rudementary running order. for each runner there is then a stopwatch brought up on screen with a start and stop button and perhaps a running time. When the runner starts you click start on his stopwatch and then stop when he finishes. I would then like these times to be put into the cell in the neighbouring column to produce the below format:

Compeitor 1 6:12
Competitor 2 12.13
etc.

The maximum number of runners on a course at any one time would be around 20 so if only 20 stopwatches could appear that would be helpful. Plus would it be possible for the stopwatches to be constantly created from a set of sequential data that can perhaps contain more information such as name? e.g.

competitor 1 Harry Thorne

the stopwatch then displays the name as well.
could these stopwatches move arounnd the screen so as one is stopped, it disappears and its position, in the top left of the screen for instance, is taken by the one most likely to finish next i.e the one with the most elapsed time?
Could a stopwatch be created every minute or so and then appear on the screen. This is because usually runners are set off at minute intervals and so if it were to pop up just before this.
one final bit. could there also be a button for 'withdrawn' so that once clicked a zero value is returned for this competitor and their stopwatch disappears.
Any ideas how i could get the information off safely/easily whilst timing to go off and be processed/printed i.e. continuous printout like an accounts calculator?
I know this is a massive ask and I will admit that I know almost nothing about excel and less about VBA but if someone could help I would be most grateful as the timing is a shambles at these competitons.

 

[tusharm]

Don't rely on XL's time functions for any 'mission critical' tasks. Just check the fraction of a second that Timer returns -- or is supposed to return. You'll be unpleasantly surprised.

There are some OS routines (called through API calls) that are much more reliable. Search this web site or the google.com archives of the XL newsgroups for the necessary code.

Chris,

Thanks for that...a great starting point. Hadn't realised you could implement something as good in such a few lines !

 

[rowett]

I tested Excel's Timer function as follows:

For 1000 times I took the Timer value and then waited until the Timer had changed and computed the difference. The results were as follows:

In this 1000 run sample:
average Timer step is exactly 0.01 (1/100th of a second)
minimum Timer step was 0.006 seconds
maximum Timer step was 0.021 seconds (roughly 1/50th of a second)

This is due to the limited resolution of the Single type used to return the result.

So it's fine as long as you're happy to be accurate to 1/50th of a second.

Regards,
Chris

 

[harry_thorne]

that's accurate enough as it relies on me hitting the button which cannot bemore accurate than that.
does anyone have any code that may be able to help me in my project?
thanks,
Harry

 

[Richie(UK)]

Hi,

As Tushar has already stated, for accurate timers in Excel you really need to make use of API code. The following is taken from "Excel 2002 VBA Programmers Reference" by mssrs Bullen, Green, Bovey, and Rosenberg.

In the VBE insert a new Class Module and name it "CHighResTimer". Now paste in the following:

'How many times per second is the counter updated?
Private Declare Function QueryFrequency Lib "kernel32" _
        Alias "QueryPerformanceFrequency" ( _
        lpFrequency As Currency) As Long

'What is the counter's value
Private Declare Function QueryCounter Lib "kernel32" _
        Alias "QueryPerformanceCounter" ( _
        lpPerformanceCount As Currency) As Long

'Variables to store the counter information
Dim mcyFrequency As Currency
Dim mcyOverhead As Currency
Dim mcyStarted As Currency
Dim mcyStopped As Currency


Private Sub Class_Initialize()
   Dim cyCount1 As Currency, cyCount2 As Currency

   'Get the counter frequency
   QueryFrequency mcyFrequency

   'Call the hi-res counter twice, to check how long it takes
   QueryCounter cyCount1
   QueryCounter cyCount2
    
   'Store the call overhead
   mcyOverhead = cyCount2 - cyCount1
End Sub


Public Sub StartTimer()
   'Get the time that you started
   QueryCounter mcyStarted
End Sub


Public Sub StopTimer()
   'Get the time that you stopped
   QueryCounter mcyStopped
End Sub


Public Property Get Elapsed() As Double
   Dim cyTimer As Currency

   'Have you stopped or not?
   If mcyStopped = 0 Then
      QueryCounter cyTimer
   Else
      cyTimer = mcyStopped
   End If

   'If you have a frequency, return the duration, in seconds
   If mcyFrequency > 0 Then
      Elapsed = (cyTimer - mcyStarted - mcyOverhead) / mcyFrequency
   End If
End Property

Now in a standard module you can make use of the timer like in this example:

Sub TestTimer()
    Dim i As Long
    Dim obTimer As New CHighResTimer
    
    obTimer.StartTimer
    'start the timer
    
    With Sheet1
        For i = 1 To 1000
            .Cells(5, 4).Value = i
        Next i
    End With
    'do your stuff, in this case increment the value in D5 from 1 to 1000
    
    obTimer.StopTimer
    'stop the timer
    
    MsgBox "1,000 iterations took " & obTimer.Elapsed & " seconds"
    'display the time taken
    
End Sub

HTH

 

[tusharm]

To use a computer (XL, specifically) to time events requires a shift in the paradigm by which such measurements are done.

Assuming that the computer is accurate enough (more specifically, in this post the Timer function is accurate enough), there is no need for a simulated stopwatch. The purpose of a stop watch is to keep time and the computer's *internal* clock already does that for you!

So, all that's needed is to 'flag' a start time and an end time (both provided by the computer).

Suppose one has a bunch of runners with names in column A. Then, suppose in column B one puts start times and in column C the end times. The necessary data for the necessary calculations are now available. In fact, one can measure all kinds of splits (by simply indicating that a particular column is for that purpose).

How does one put in a start time? The easiest is with the code below. Enter a 1 in a cell and TAB (or ENTER) to complete data entry. The code below will replace the 1 with the current value of the Timer routine. Indicate multiple concurrent start/stop events by selecting multiple cells, entering 1 and pressing CTRL+ENTER.

So, to clock the start time of a person whose name is in A10, click in B10, enter a 1 and TAB out of the cell. To enter the stop time of that person, enter a 1 in C10 and TAB out.

The code below goes into the Worksheet module. I'll leave a more general solution (one that works with any workbook and/or any worksheet) to you.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim aCell As Range
    On Error Resume Next
    Application.EnableEvents = False
    For Each aCell In Target
        If aCell.Value = 1 Then aCell.Value = Timer
        Next aCell
    Application.EnableEvents = True
    End Sub

 

[Emily]

HOWTO: Use QueryPerformanceCounter to Time Code
The information in this article applies to:
Microsoft Excel 2000
Microsoft Visual Basic Learning Edition for Windows 5.0
Microsoft Visual Basic Learning Edition for Windows 6.0
Microsoft Visual Basic Professional Edition for Windows 5.0
Microsoft Visual Basic Professional Edition for Windows 6.0
Microsoft Visual Basic Enterprise Edition for Windows 5.0
Microsoft Visual Basic Enterprise Edition for Windows 6.0
Microsoft Visual Basic Control Creation Edition for Windows 5.0
Microsoft Visual Basic Standard Edition, 32-bit, for Windows 4.0
Microsoft Visual Basic Professional Edition, 32-bit, for Windows 4.0
Microsoft Visual Basic Enterprise Edition, 32-bit, for Windows 4.0
Microsoft Access 2002
Microsoft Access 2000
Microsoft Access 97
Microsoft Access for Windows 95 7.0
Microsoft Excel 2002
Microsoft Excel 97 for Windows
Microsoft Excel for Windows 95 7.0
Microsoft Word 2002
Microsoft Word 2000
Microsoft Word 97 for Windows

This article was previously published under Q172338
SUMMARY
When timing code to identify performance bottlenecks, you want to use the highest resolution timer the system has to offer. This article describes how to use the QueryPerformanceCounter function to time application code.
MORE INFORMATION
Several timers of differing accuracy are offered by the operating system: Function Units Resolution
---------------------------------------------------------------------------
Now, Time, Timer seconds 1 second
GetTickCount milliseconds approx. 10 ms
TimeGetTime milliseconds approx. 10 ms
QueryPerformanceCounter QueryPerformanceFrequency same

If your system supports a high-resolution counter, you can use QueryPerformanceCounter and QueryPerformanceFrequency to do high-resolution timings.

The following sample code compares the various counters:

WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWN RISK. Microsoft provides this code "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.
Step-by-Step Procedures
Enter the following code into a Module. If you enter it into a class, form, or report module, make the declarations Private. Option Explicit

Declare Function QueryPerformanceCounter Lib "Kernel32" _
(X As Currency) As Boolean
Declare Function QueryPerformanceFrequency Lib "Kernel32" _
(X As Currency) As Boolean
Declare Function GetTickCount Lib "Kernel32" () As Long
Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub Test_Timers()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Count1 As Long, Count2 As Long, Loops As Long
'
' Time QueryPerformanceCounter
'
If QueryPerformanceCounter(Ctr1) Then
QueryPerformanceCounter Ctr2
Debug.Print "Start Value: "; Format$(Ctr1, "0.0000")
Debug.Print "End Value: "; Format$(Ctr2, "0.0000")
QueryPerformanceFrequency Freq
Debug.Print "QueryPerformanceCounter minimum resolution: 1/" & _
Freq * 10000; " sec"
Debug.Print "API Overhead: "; (Ctr2 - Ctr1) / Freq; "seconds"
Else
Debug.Print "High-resolution counter not supported."
End If
'
' Time GetTickCount
'
Debug.Print
Loops = 0
Count1 = GetTickCount()
Do
Count2 = GetTickCount()
Loops = Loops + 1
Loop Until Count1 <> Count2
Debug.Print "GetTickCount minimum resolution: "; _
(Count2 - Count1); "ms"
Debug.Print "Took"; Loops; "loops"
'
' Time timeGetTime
'
Debug.Print
Loops = 0
Count1 = timeGetTime()
Do
Count2 = timeGetTime()
Loops = Loops + 1
Loop Until Count1 <> Count2
Debug.Print "timeGetTime minimum resolution: "; _
(Count2 - Count1); "ms"
Debug.Print "Took"; Loops; "loops"
End Sub

Run the function from the Debug/Immediate window. Your output should appear similar to the following:
Start Value: 3516284.3498
End Value: 3516284.3521
QueryPerformanceCounter minimum resolution: 1/1193182 sec
API Overhead: 1.92761875388667E-05 seconds

GetTickCount minimum resolution: 10 ms
Took 650 loops

timeGetTime minimum resolution: 10 ms
Took 1565 loops

Multiple statements execute before either GetTickCount or timeGetTime record a change. The actual number of loops will vary depending on the background tasks the operating system is executing.

On the other hand, QueryPerformanceCounter changes value between successive API calls, indicating its usefulness in high-resolution timing. The resolution in this case is on the order of a microsecond. Because the resolution is system-dependent, there are no standard units that it measures. You have to divide the difference by the QueryPerformanceFrequency to determine the number of seconds elapsed. In the case above, the overhead for just calling the API is about 19 microseconds. This would have to be subtracted when timing other code as follows: Private Sub Time_Addition()
Dim Ctr1 As Currency, Ctr2 As Currency, Freq As Currency
Dim Overhead As Currency, A As Long, I As Long
QueryPerformanceFrequency Freq
QueryPerformanceCounter Ctr1
QueryPerformanceCounter Ctr2
Overhead = Ctr2 - Ctr1 ' determine API overhead
QueryPerformanceCounter Ctr1 ' time loop
For I = 1 To 100
A = A + I
Next I
QueryPerformanceCounter Ctr2
Debug.Print "("; Ctr1; "-"; Ctr2; "-"; Overhead; ") /"; Freq
Debug.Print "100 additions took";
Debug.Print (Ctr2 - Ctr1 - Overhead) / Freq; "seconds"
End Sub

Sample output:
( 3630876.6256 - 3630876.6388 - 0.0013 ) / 119.3182
100 additions took 9.97333181358753E-05 seconds

NOTE: Because currency variables are used, the values returned are 10000 times smaller than the actual counters. Because the calculation of seconds involves a division operation, this factor is cancelled out.
REFERENCES
Microsoft Developer Network; topics: timeGetTime GetTickCount QueryPerformanceCounter QueryPerformanceFrequency
Last Reviewed: 5/12/2003
Keywords: kbhowto kbProgramming KB172338

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q172338