Timer

[sspatriots]

Good morning,

I am using this timer I found online to track time spent on different jobs task throughout the day. Seems to start and stop ok during the day. I minimized the screen over night and came in this morning and opened it up and the timer was still running (which is what I was testing out). However, as soon as I selected the stop button, the time went from 15 hours back to 8 hours. If I select the "Start" button again it will resume from the 8 hour number that it when back to. The "Reset" button is set up to copy the total time when ready to clear the field back to all zeroes and allow the user to put that value in a cell in a column that tallies the total time on the job. Also, I'm attaching a screenshot of an error message that another user got this morning when he closed the file and reopened it. Just looking to clean this up to avoid the errors. Seems to work fine as long as the screen is open during the day and stays open. Any help on this would be much appreciated.


Thanks, SS

Option Explicit

' Written by Philip Treacy, My Online Training Hub
' https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba

Sub StartTimer()

    Dim Start As Single, RunTime As Single, CurrentlyElapsed As Single
    Dim ElapsedTime As String
    
    'Set the control cell to 0 and make it green
    Range("B1").Value = 0
    Range("C4").Interior.Color = 13561798 'Light Green
    Range("C4").Font.Color = 24832 'Dark Green

    'If Range("D1").Value <> 0 Then CurrentlyElapsed = Range("D1").Value
    
    Start = Timer    ' Set start time.
    Debug.Print Start
    Do While Range("B1").Value = 0
        
        DoEvents    ' Yield to other processes.
        RunTime = Timer    ' current elapsed time
        CurrentlyElapsed = RunTime - Start + Range("D1").Value
        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm")
        'Display currently elapsed time in C4
        Range("C4").Value = ElapsedTime
        Application.StatusBar = ElapsedTime
            
    Loop
        
    Range("C4").Value = ElapsedTime
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = CurrentlyElapsed
    Application.StatusBar = False

End Sub

Sub StopTimer()

    'Set the control cell to 1
    Range("B1").Value = 1
    
End Sub

Sub ResetTimer()

Dim myRange As Range
Dim CopyRange As Range

Set CopyRange = Range("C4")

On Error Resume Next
Set myRange = Application.InputBox(Prompt:="Select Cell you want to capture your total time in.", Title:="Format Titles", Type:=8)

If myRange Is Nothing Then
    MsgBox "No selection made", vbCritical, "Input required"
    Exit Sub
End If

myRange.Value = CopyRange.Value

    If Range("B1").Value > 0 Then
       
        'Set the control cell to 1
        Range("C4").Value = Format(0, "hh:mm:ss")
'        Range("C4").Value = Format(0, "hh:mm")
        Range("C4").Interior.Color = 10284031 'light Gold
        Range("C4").Font.Color = 22428 'Dark Brown
        Range("D1").Value = 0
    
    End If
    
End Sub

[ATTACH type="full"]97192[/ATTACH][ATTACH type="full"]97193[/ATTACH]

 

[offthelip]

Your problem is that the timer function returns a SINGLE variable , and on Windows it is returning a fraction of seconds so your count overflows. Try this simple modification which uses the TIME function which returns a variant and takes years to over flow!!

Option Explicit

' Written by Philip Treacy, My Online Training Hub
' https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba

Sub StartTimer()

    Dim Start As Single, RunTime As Single, CurrentlyElapsed As Single
    Dim ElapsedTime As String
    
    'Set the control cell to 0 and make it green
    Range("B1").Value = 0
    Range("C4").Interior.Color = 13561798 'Light Green
    Range("C4").Font.Color = 24832 'Dark Green

    'If Range("D1").Value <> 0 Then CurrentlyElapsed = Range("D1").Value
    
'    Start = Timer    ' Set start time.
    Start = Time    ' Set start time.
    Debug.Print Start
    Do While Range("B1").Value = 0
        
        DoEvents    ' Yield to other processes.
 '       RunTime = Timer    ' current elapsed time
 '       CurrentlyElapsed = RunTime - Start + Range("D1").Value
        CurrentlyElapsed = Time - Start
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
         ElapsedTime = Format(CurrentlyElapsed, "hh:mm:ss")
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm")
        'Display currently elapsed time in C4
        Range("C4").Value = ElapsedTime
        Application.StatusBar = ElapsedTime
            
    Loop
        
    Range("C4").Value = ElapsedTime
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = CurrentlyElapsed
    Application.StatusBar = False

End Sub

 

[offthelip]

Apologies I didnt change the declarations which I should have:

 Dim Start As Variant, RunTime As Variant, CurrentlyElapsed As Variant
    Dim ElapsedTime As Variant

Once Excel would have done better if you hadn't declared them at all and got rid of option explicit!!

 

[sspatriots]

That seems to be working so far. Is there a way to make this clock to resume from the time showing if I select "START" again after using the "STOP" button to pause it temporarily. The purpose of our clock is so that our Project Managers can pause the job they are working long enough to deal with routine daily interruptions and then resume when they get back on the job in front of them. I think that part was working in the original code.


Thanks, SS

 

[sspatriots]

I just minimized it at around 2 minutes. I opened it up about10 minutes later and it said 3 minutes on there and would not start back up. It looks like it stopped at 3 minutes while the window was minimized.

 

[offthelip]

Try moving the the declaration of START right up to the top of the module above the sub call and making it public, that means it is saved al the time EXCEL is open
I hadn't realised your requirement to stop and start the timer, so I hadn't looked at that, I don't have time at the moment but might do later or tomorrow

Public start As Variant
Sub StartTimer()

 

[sspatriots]

This is what I did, but it still seems to want to start the clock again at 0:00:00 when I try and resume with "START" after selecting the "STOP" button to temporarily pause it.

Option Explicit
' Written by Philip Treacy, My Online Training Hub
' https://www.myonlinetraininghub.com/timer-stopwatch-excel-vba
Public Start As Variant
Sub StartTimer()

    Dim RunTime As Variant, CurrentlyElapsed As Variant
    Dim ElapsedTime As Variant
    
    'Set the control cell to 0 and make it green
    Range("B1").Value = 0
    Range("C4").Interior.Color = 13561798 'Light Green
    Range("C4").Font.Color = 24832 'Dark Green

    'If Range("D1").Value <> 0 Then CurrentlyElapsed = Range("D1").Value
    
'    Start = Timer    ' Set start time.
    Start = Time    ' Set start time.
    Debug.Print Start
    Do While Range("B1").Value = 0
        
        DoEvents    ' Yield to other processes.
 '       RunTime = Timer    ' current elapsed time
 '       CurrentlyElapsed = RunTime - Start + Range("D1").Value
        CurrentlyElapsed = Time - Start
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm:ss")
         ElapsedTime = Format(CurrentlyElapsed, "hh:mm:ss")
'        ElapsedTime = Format(CurrentlyElapsed / 86400, "hh:mm")
        'Display currently elapsed time in C4
        Range("C4").Value = ElapsedTime
        Application.StatusBar = ElapsedTime
            
    Loop
        
    Range("C4").Value = ElapsedTime
    Range("C4").Interior.Color = 13551615 'light Red
    Range("C4").Font.Color = 393372 'Dark Red
    Range("D1").Value = CurrentlyElapsed
    Application.StatusBar = False

End Sub

 

[sspatriots]

Not sure if any of the information in the post on this forum below would of benefit or not, but it came up when I was looking for a solution. Just not experienced enough to know if it is applicable or not.

OnTime macro stops running when Excel minimized

 

[rondeondo]

you can also just use a variable to store start time and end time when the process finishes, then it doesn't matter if Excel goes to sleep

 

[johnnyL]

@sspatriots When you click the stop button, are you saying that the button should actually be a 'Pause' button? In other words if you wait for 5 minutes after clicking the 'Stop' button, & then select the 'Start' button, should the time being displayed start at the 5 minutes later or should it start displaying the time right after the the 'Stop' button was initially pushed?