I have a worksheet which I use to track progress recording audiobooks (I'm a narrator). On each sheet, I have a timer that I use to track how many hours I record each day/session.
If the timer is running at midnight, it sets the timer to 20:00:00 and keeps going. I often am recording at midnight, so this is a major issue if I don't remember to stop the timer before midnight and restart it after.
Here is a link to the whole Excel file. You can find the timer in the TEMPLATE worksheet. The Macros for the timer are within Module1.
http://recording-tracker.narratedby.me
Here is all the code:
Option Explicit
Dim NextTick As Date, t As Date, PreviousTimerValue As Date, strSheetName As String, lRowTime As Long, lRowDate As Date, CurrentTimerValue As Date, StartTimerValue As Date
Sub StartTime()
strSheetName = ActiveSheet.Name
StartTimerValue = PreviousTimerValue
CurrentTimerValue = PreviousTimerValue
t = Time
Call ExcelstrSheetName
End Sub
Private Sub ExcelstrSheetName()
strSheetName = ActiveSheet.Name
ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value = Format(Time - t + PreviousTimerValue, "hh:mm:ss")
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "ExcelstrSheetName"
End Sub
Sub StopClock()
strSheetName = ActiveSheet.Name
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="ExcelstrSheetName", schedule:=False
CurrentTimerValue = ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value
End Sub
Sub Reset()
strSheetName = ActiveSheet.Name
CurrentTimerValue = ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value
On Error Resume Next
Application.OnTime earliesttime:=NextTick, procedure:="ExcelstrSheetName", schedule:=False
ThisWorkbook.Worksheets(strSheetName).Range("Q1").Value = 0
End Sub
The cell Q1 is what holds the timer value at any given time.
I have removed other Subs and portions of these Subs which are irrelevant to my question. This is the basic stopwatch function.
Any help is much appreciated!
question from:
https://stackoverflow.com/questions/65713825/excel-vba-stopwatch-breaking-at-midnight 与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…