Stopwatch in Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tom Corker - 07 Mar 2008 23:29 GMT

I'm creating a spreadsheet for logging railway performance timings. I
need a stopwatch in excel that can give a split / lap reading,
preferably when the enter key is pressed. I reckon it can be done with
the now() function, as this can give the needed accuracy (1/100
second) if the format hh:mm:ss.00 is used. I've not got much
experience with macros etc, so I would be very grateful for any
suggestions on ways to go about this. Ideally, when the split button
is pressed, it will give the split time and the difference from the
previous split time in a new cell, so by the end I have a list of
times. I will be able to use these to calculate average speeds etc.

Thanks.

Reply to this Message

Gary''s Student - 08 Mar 2008 00:16 GMT

Here is just the start. You can adapt.

1. Format column A as hh:mm:ss.000
2. In a standard module enter:

Public i as Integer

3. In the worksheet code area enter the following event macro:

Private Sub Worksheet_Calculate()
i = i + 1
Application.EnableEvents = False
Cells(i, "A") = Now
Application.EnableEvents = True
End Sub

In an un-used cell, not in column A, enter:

=NOW()

Every time you touch F9 the time will be recorded in the next free cell in
column A.

To get splits/durations, put difference equations in column B

Signature

Gary''s Student - gsnu200772

> I'm creating a spreadsheet for logging railway performance timings. I
> need a stopwatch in excel that can give a split / lap reading,
[quoted text clipped - 8 lines]
>
> Thanks.

Reply to this Message

Billy Liddel - 08 Mar 2008 09:54 GMT

Hi

I see you already has a solution from Gary. Here is mine. I placed the NOW
formula
in Cell D1. The code is placed in the Sheet Module so right click on the
sheet tab select View Code and paste in the code. Use a different sheet for
this code so there is no conflict.

Private Sub Worksheet_Calculate()
Set myTime = Range("D1")
Set rng = Range("A:A")
i = WorksheetFunction.CountA(rng)
i = i + 1
Application.EnableEvents = False
Cells(i, "A") = myTime
Cells(i, "A").NumberFormat = "dd/mm/yy hh:mm:ss.00"
If i >= 3 Then
Cells(i, "B") = Cells(i, "A") - Cells(i - 1, "A")
Cells(i, "B").NumberFormat = "mm ""Mins"" :ss.00 ""Secs"""
End If
Application.EnableEvents = True
End Sub

Hope this helps

Peter

> I'm creating a spreadsheet for logging railway performance timings. I
> need a stopwatch in excel that can give a split / lap reading,
[quoted text clipped - 8 lines]
>
> Thanks.

Reply to this Message

Billy Liddel - 08 Mar 2008 10:07 GMT

Tom

This revised macro adds the Average formula into the sheet as well

Private Sub Worksheet_Calculate()
Dim addr As String, i As Long
Set myTime = Range("D1")
Set rng = Range("A:A")
i = WorksheetFunction.CountA(rng)
i = i + 1
Application.EnableEvents = False
Cells(i, "A") = myTime
Cells(i, "A").NumberFormat = "dd/mm/yy hh:mm:ss.00"
If i >= 3 Then
Cells(i, "B") = Cells(i, "A") - Cells(i - 1, "A")
Cells(i, "B").NumberFormat = "mm ""Mins"" :ss.00 ""Secs"""
Cells(i, "C").Formula = "=AVERAGE(B1:B" & i & ")"
Cells(i, "C").NumberFormat = "mm ""min"" ss.00 ""Secs"" "
End If
Application.EnableEvents = True
End Sub

Reply to this Message

Billy Liddel - 08 Mar 2008 11:07 GMT

Tom

The trouble with these macros is that if you type something into a cell and
press enter the macro will run and enter a new time when you might not have
wanted it. The solution is to set Calculation to Manual in Tools, Options but
then Excel will not automatically cal in any workbook.

The following code will fix this for you. It is entered in the Workbook
Module. Right-Click the Excel Icon on the File Toolbar and click View Code
and paste the code below into the workbook module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Private Sub Workbook_Deactivate()
With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Private Sub Workbook_Open()
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub
Private Sub Workbook_Activate()
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False
End Sub

Regards
Peter

Reply to this Message

Gary''s Student - 08 Mar 2008 11:14 GMT

This is a very nice solution.

Signature

Gary''s Student - gsnu2007e

> Tom
>
[quoted text clipped - 17 lines]
> Application.EnableEvents = True
> End Sub

Reply to this Message

Billy Liddel - 08 Mar 2008 11:48 GMT

Thanks Gary!

> This is a very nice solution.
>
[quoted text clipped - 19 lines]
> > Application.EnableEvents = True
> > End Sub

Reply to this Message

Rate this thread: