application.ontime

[dan_l]

I don't think I've had call to use this before, but I've got a little project:


Basically, I want an alert to pop every hour. I'm testing application on time, but the results are being a little fuzzy.


Here's the code:

Sub time()


Application.OnTime Now + TimeValue("00:01:00"), "timecall"


End Sub


Sub timecall()

Dim ticker As Integer

ticker = ActiveSheet.UsedRange.Rows.Count + 1

Dim check As Integer

check = Minute(Now) Mod 5


If check = 0 Then

Sheets("log").Cells(ticker, 1).Value = "passed test at " & Now

Else: Sheets("log").Cells(ticker, 1).Value = "failed test at " & Now

End If


Call time


End Sub

Here's an output log from my test:

log

failed test at 7/1/2012 2:18:32 PM

failed test at 7/1/2012 2:18:32 PM

failed test at 7/1/2012 2:18:39 PM

failed test at 7/1/2012 2:18:44 PM

failed test at 7/1/2012 2:18:56 PM

failed test at 7/1/2012 2:19:10 PM

failed test at 7/1/2012 2:19:33 PM

failed test at 7/1/2012 2:19:33 PM

failed test at 7/1/2012 2:19:39 PM

failed test at 7/1/2012 2:19:44 PM

failed test at 7/1/2012 2:19:56 PM

passed test at 7/1/2012 2:20:10 PM

passed test at 7/1/2012 2:20:33 PM

passed test at 7/1/2012 2:20:33 PM

passed test at 7/1/2012 2:20:39 PM

passed test at 7/1/2012 2:20:44 PM

passed test at 7/1/2012 2:20:56 PM

failed test at 7/1/2012 2:21:10 PM

failed test at 7/1/2012 2:21:33 PM

failed test at 7/1/2012 2:21:33 PM

failed test at 7/1/2012 2:21:39 PM

failed test at 7/1/2012 2:21:44 PM

failed test at 7/1/2012 2:21:56 PM

failed test at 7/1/2012 2:22:10 PM


You can see it is triggering a desired event at the right time, but it's doing it 3 times actually. And, even though I put a timer up for 1 minute, it seems to go more than once a minute. I _think_ it's because in the first procedure, it calls my check every time it cycles, and the check calls the first procedure as soon as it's done.


In short: I think I'm in the right book, but on the wrong page.


Can anybody help a bit?

 

[xld]

Not sure, but try this

[pre]

Private nTime As Double

Sub time()
If nTime <> 0 Then Application.OnTime nTime, "timecall", , False
nTime = Now + TimeValue("00:01:00")
Application.OnTime nTime, "timecall"
End Sub

Sub timecall()
Dim ticker As Integer
Dim check As Integer
ticker = ActiveSheet.UsedRange.Rows.Count + 1
check = Minute(Now) Mod 5

If check = 0 Then

Sheets("log").Cells(ticker, 1).Value = "passed test at " & Now
Else

Sheets("log").Cells(ticker, 1).Value = "failed test at " & Now
End If

Call time
End Sub

[/pre]

 

[NARAYANK991]

Hi Dan ,


When I copied your code into a blank worksheet , its behaviour is correct and precise , down to the second.


The OnTime statement schedules a call to the procedure which is passed as a parameter , based on the interval mentioned in the second parameter. There is no reason for it to behave erratically or wrongly , unless it is called more frequently than it should be.


To test this , I eliminated the IF statement in the timecall procedure , and reduced the OnTime frequency to 1 second instead of your 1 minute. Everything worked flawlessly.


Narayan


P.S. A lot of info is available here : http://www.cpearson.com/excel/OnTime.aspx