Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
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.....! -- Karthi |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
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, _ Procedu="Clock", _ Schedule:=False If Err.Number 0 Then Exit Sub On Error GoTo 0 End Sub -- Regards, OssieMac "Karthik" wrote: 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.....! -- Karthi |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
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") -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
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, _ Procedu="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 -- Regards, OssieMac |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
Chip Pearson explains application.ontime at:
http://www.cpearson.com/excel/OnTime.aspx Karthik wrote: 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.....! -- Karthi -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
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 "Karthik" skrev i melding ... 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.....! -- Karthi |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
Thank you so much for this piece of code. I am also looking for something exactly like this. My problem is that the stop clock macro didn't work. Any suggestions? I tried adding a button to trigger the macro and just running the macro through the excel menu bar, and neither worked. OssieMac;598379 Wrote: 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, _ Procedu="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 -- Regards, OssieMac -- apandbp ------------------------------------------------------------------------ apandbp's Profile: http://www.thecodecage.com/forumz/member.php?u=550 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165832 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Stop timer.
apandbp;740473 Wrote: Thank you so much for this piece of code. I am also looking for something exactly like this. My problem is that the stop clock macro didn't work. Any suggestions? I tried adding a button to trigger the macro and just running the macro through the excel menu bar, and neither worked. Maybe you need to re-read the thread, there is a stop timer macro there and it works as it should. -- Simon Lloyd Regards, Simon Lloyd 'Microsoft Office Help' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?u=1 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=165832 http://www.thecodecage.com/forumz --- news://freenews.netfront.net/ - complaints: --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Timer w/ Start, Stop, Pause | Excel Programming | |||
How to stop 'Egg Timer' | Excel Programming | |||
Stopping a Timer / Running a timer simultaneously on Excel | Excel Discussion (Misc queries) | |||
How do I stop other circles in other cells to stop selecting? | Excel Worksheet Functions | |||
stop timer | Excel Programming |