Seconds Counter

[maninjapan]

I need a timer as part of a function to update a cell every X seconds, I currently use a clock function to keep time. I thought this solved my problem, however it is only good for up to 60 seconds.
I would like to add a cell that just keeps counting the seconds.
For example from 09:05:00 - 09:09:00 would show as 240.
Any help on this would be much appreciated.

[Bob Phillips]

Option Explicit

Public nTime As Double

Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
    nTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime nTime, "RunTimer"
End Sub

Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

[maninjapan]

Thanks XLD, thats just what I wanted. What would be the line of code to add under a command button to execute this ?

[Bob Phillips]

Add two buttons from the forms menu, and assign RunTimer to one and StopTimer to another.

[maninjapan]

Sorry XLD, yeah Im not quite sure how to actually assign it.....

Do I just add the code under the button_Click?? (somehow I dont think so)

Private Sub CommandButton1_Click()

Option Explicit
 
Public nTime As Double
 
Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
    nTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime nTime, "RunTimer"
End Sub

[Bob Phillips]

You don't. If you create buttons from the forms toolbar as I suggested, when you copy it from the toolbar to the worksheet, it will open up a dialog to asign a macro. That is when you selct the RunTimer macro and so on.

[maninjapan]

Ok got it, thanks for your help (again). What would I add to reset the timer when it stops?

[Bob Phillips]

Sub ResetTimer() 
Range("A1").Value = 0
    call RunTimer
End Sub

[maninjapan]

Thanks again. I noticed though that after just clicking the reset button It would start counting in 2's and 3's each second. As if it was running the timer 2 or 3 at a time.

[Bob Phillips]

I had assumed you would stop the timer before reset, but I guess you didn't, which means that you had two timers running, hence the od behaviour.

We can force a stop on reset to be sure

Sub ResetTimer()
Call StopTimer
    Range("A1").Value = 0
    Call RunTimer
End Sub

[maninjapan]

XLD, yes thats what I would normally do. I just found it out by mistake actually, but this makes it easier still.

I would a cell to return TRUE/FALSE every N seconds of the timer.

A1 = TIMER A2 = Seconds

=IF(A1/A2 = 'Whole Number', TRUE,FALSE)

[Bob Phillips]

Sorry, you have lost me with that last post.

[maninjapan]

Sorry. Now that I have the timer working in A1 Im trying to get a TRUE/FALSE statement working in A2. So if B1="10" then every 10 seconds A2 will change from "FALSE" to "TRUE". Hope that makes sense....

[Bob Phillips]

So, are you saying that what you really want is to put a value in B1, and when you start, reset the timer, it alternates A2 between TRUEand FALSE every B1 seconds?

[maninjapan]

So, are you saying that what you really want is to put a value in B1, and when you start, reset the timer, it alternates A2 between TRUEand FALSE every B1 seconds?

That is correct. every B1 seconds A2 will show true, the rest of the time it will show false

[Bob Phillips]

Public nTime As Double
 
Sub RunTimer()
Range("A2").Value = Not CBool(Range("A2").Value)
    nTime = Now + TimeSerial(0, 0, Range("B1").Value)
    Application.OnTime nTime, "RunTimer"
End Sub
 
Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

Sub ResetTimer()
Call StopTimer
    Range("A2").Value = False
    Call RunTimer
End Sub

[maninjapan]

XLD, thats getting close. if B2 = 10 then it only shows true every 10th second. So it would show FALSE for 9 seconds and the TRUE for the 10th second. Having the timer actually still showing in A1 would also be a help to see where it is at.

I tried the following, but the timer in A1 doesn't count every second if I change B1

Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
    Range("A2").Value = Not CBool(Range("A2").Value)
    nTime = Now + TimeSerial(0, 0, Range("B1").Value)
    Application.OnTime nTime, "RunTimer"
End Sub

[Bob Phillips]

Public nTime1 As Double
Public nTime2 As Double
 
Sub StartTimers()
Range("A1").Value = 0
    Call RunTimer1
    Range("A2").Value = False
    Call RunTimer2
End Sub

Sub RunTimer1()
Range("A1").Value = Range("A1").Value + 1
    nTime1 = Now + TimeSerial(0, 0, 1)
    Application.OnTime nTime1, "RunTimer1"
End Sub

Sub RunTimer2()
Range("A2").Value = Not CBool(Range("A2").Value)
    nTime2 = Now + TimeSerial(0, 0, Range("B1").Value)
    Application.OnTime nTime2, "RunTimer2"
End Sub
 
Sub StopTimer()
Application.OnTime nTime1, "RunTimer1", , False
    Application.OnTime nTime2, "RunTimer2", , False
End Sub

Sub ResetTimers()
Call StopTimer
    Range("A1").Value = 0
    Call RunTimer1
    Range("A2").Value = False
    Call RunTimer2
End Sub

[maninjapan]

cool, i got the timer up now, I just need the cell to show TRUE every B1th second. for example if B1 = 5 :
FALSE FALSE FALSE FALSE TRUE FALSE FALSE FALSE FALSE TRUE

currently if B1 = 5:
TRUE, TRUE, TRUE, TRUE, TRUE, FALSE, FALSE, FALSE, FALSE, FALSE

[Bob Phillips]

Public nTime As Double
 
Sub StartTimer()
Range("A1").Value = 0
    Range("A2").Value = False
    Call RunTimer
End Sub

Sub RunTimer()
Range("A1").Value = Range("A1").Value + 1
    If Range("A1").Value Mod Range("B1").Value = 0 Then
        Range("A2").Value = True
    Else
        Range("A2").Value = False
    End If
    nTime = Now + TimeSerial(0, 0, 1)
    Application.OnTime nTime, "RunTimer"
End Sub
 
Sub StopTimer()
Application.OnTime nTime, "RunTimer", , False
End Sub

Sub ResetTimer()
Call StopTimer
    Range("A1").Value = 0
    Range("A2").Value = False
    Call RunTimer
End Sub