Stopwatch in Excel

[Tom Corker]

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.

 

[Gary''s Student]

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

 

[Billy Liddel]

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

 

[Billy Liddel]

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

 

[Billy Liddel]

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

 

[Gary''s Student]

This is a very nice solution.

 

[Billy Liddel]

Thanks Gary!

This is a very nice solution.