Stop timer.

[Karthik]

Hi All,

I copied a script from discussions given below to prepare a rudementary clock.

Sub Clock()
ThisWorkbook.Sheets("Sheet1").Range("g7").Value = CDbl(Time)
NextTick = Now + TimeValue("00:00:01")
Application.OnTime NextTick, "Clock"
End Sub

I would like to have a seperate macro to stop this clock from calculating.

Thanks for your help in advance.....!

 

[OssieMac]

Hi Karthi,

Note that NextTick need to be declared in the declarations area at the top
of the module so that it is available to all subs in the module.

Dim NextTick

Sub StopClock()
'Stop OnTime event.
'Returns error if clock already stopped and hense the On Error handling.
On Error Resume Next
Application.OnTime _
EarliestTime:=NextTick, _
Procedure:="Clock", _
Schedule:=False
If Err.Number > 0 Then Exit Sub
On Error GoTo 0
End Sub

 

[OssieMac]

Also CDbl(Time) appears to be a UDF (User Defined Function). You could simply
use the following in lieu. (Set the fomat between the double quotes to
whatever format you want.)

The space and underscore at the end of the line is a line break in an
otherwise single line of code.

ThisWorkbook.Sheets("Sheet1").Range("g7").Value _
= Format(Now(), "hh:mm:ss")

 

[OssieMac]

Hello yet again Karthi,

Your question prompted me to write some code that more fully covers timers.
It records the start time in cell A1, then the progressive time is displayed
in cell A2 and after the timer is stopped the total elapsed time is displayed
in cell A3.

Unlike the times entered as text with the format function as per my previous
post, these times can be added and subtracted etc (used in maths equations
etc).

You start the timer from Sub StartTiming()

Dim NextTick 'Must be declared in the declarations section before any subs.

Sub StartTiming()

Call StartClock

With ThisWorkbook.Sheets("Sheet1")
'Clear total elapsed time
.Range("A3").ClearContents

'Format the cells with time formats
.Range("A1:A3").NumberFormat = "hh:mm:ss"

'Save the start time in cell A1
.Range("A1").Value = Range("A2").Value

End With

End Sub

Sub StartClock()

With ThisWorkbook.Sheets("Sheet1")
.Range("A2") = Now()
End With

NextTick = Now + TimeValue("00:00:01")

Application.OnTime NextTick, "StartClock"
End Sub

Sub StopClock()
'Stop OnTime event.
'Returns error if already stopped and hense the on error handling.
On Error Resume Next

Application.OnTime _
EarliestTime:=NextTick, _
Procedure:="StartClock", _
Schedule:=False

If Err.Number > 0 Then Exit Sub

On Error GoTo 0
With ThisWorkbook.Sheets("Sheet1")
.Range("A3").Value _
= .Range("A2").Value - .Range("A1").Value
End With

End Sub

 

[Dave Peterson]

Chip Pearson explains application.ontime at:
http://www.cpearson.com/excel/OnTime.aspx

 

[Harald Staff]

Hi Karthi

For a simple solution, do a test for something before Application.ontime.
Here you stop it by putting anything in the neighbor cell:

If ThisWorkbook.Sheets("Sheet1").Range("h7").Value <> "" Then Exit Sub
Application.OnTime Nexttick, "Clock"

A boolean public variable is probably a better choice.

HTH. Best wishes Harald