Error using OnTime Method

[Sam]

I am testing a simple OnTime procedure and receive an error 1004 upon
execution. Entire code is:
Sub SetAlarm()
Application.OnTime TimeValue("10:35:00 am"), "RunTest"
End Sub

Sub RunTest()
Range("A50").End(xlUp).Offset(1, 0).Value = Date
End Sub

Sub SetAnotherAlarm()
Application.OnTime EarliestTime:=TimeValue("10:48:00 am"),
Procedure:="RunTest", Schedule:=False
End Sub

The first OnTime procedure runs fine, but when I add the optional
"Schedule", I get an error "Method OnTime of object _Application failed. When
I select Help from the Debugger, I get a blank page. VBA help is installed.

Can anyone help?

[Chip Pearson]

Sam,

To use the Schedule:=False argument to cancel an OnTime
procedure, you must provide OnTime with the *exact* time that the
event was scheduled. See www.cpearson.com/excel/ontime.htm for
details and example code.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



"Sam" <[email protected]> wrote in message
news:[email protected]...
>I am testing a simple OnTime procedure and receive an error 1004
>upon
> execution. Entire code is:
> Sub SetAlarm()
> Application.OnTime TimeValue("10:35:00 am"), "RunTest"
> End Sub
>
> Sub RunTest()
> Range("A50").End(xlUp).Offset(1, 0).Value = Date
> End Sub
>
> Sub SetAnotherAlarm()
> Application.OnTime EarliestTime:=TimeValue("10:48:00 am"),
> Procedure:="RunTest", Schedule:=False
> End Sub
>
> The first OnTime procedure runs fine, but when I add the
> optional
> "Schedule", I get an error "Method OnTime of object
> _Application failed. When
> I select Help from the Debugger, I get a blank page. VBA help
> is installed.
>
> Can anyone help?

[JE McGimpsey]

You can't clear (by using Schedule = False) a procedure that hasn't been
scheduled yet.


Here's the Help article:

> OnTime Method
>
> Schedules a procedure to be run at a specified time in the future
> (either at a specific time of day or after a specific amount of time
> has passed).
>
> Syntax
>
> expression.OnTime(EarliestTime, Procedure, LatestTime, Schedule)
>
> expression Required. An expression that returns an Application object.
>
> EarliestTime Required Variant. The time when you want this procedure to be
> run.
>
> Procedure Required String. The name of the procedure to be run.
>
> LatestTime Optional Variant. The latest time at which the procedure can be
> run. For example, if LatestTime is set to EarliestTime + 30 and Microsoft
> Excel is not in Ready, Copy, Cut, or Find mode at EarliestTime because
> another procedure is running, Microsoft Excel will wait 30 seconds for the
> first procedure to complete. If Microsoft Excel is not in Ready mode within
> 30 seconds, the procedure won't be run. If this argument is omitted,
> Microsoft Excel will wait until the procedure can be run.
>
> Schedule Optional Variant. True to schedule a new OnTime procedure. False
> to clear a previously set procedure. The default value is True.
>
> Remarks
>
> Use Now + TimeValue(time) to schedule something to be run when a specific
> amount of time (counting from now) has elapsed. Use TimeValue(time) to
> schedule something to be run a specific time.


In article <[email protected]>,
Sam <[email protected]> wrote:

> I am testing a simple OnTime procedure and receive an error 1004 upon
> execution. Entire code is:
> Sub SetAlarm()
> Application.OnTime TimeValue("10:35:00 am"), "RunTest"
> End Sub
>
> Sub RunTest()
> Range("A50").End(xlUp).Offset(1, 0).Value = Date
> End Sub
>
> Sub SetAnotherAlarm()
> Application.OnTime EarliestTime:=TimeValue("10:48:00 am"),
> Procedure:="RunTest", Schedule:=False
> End Sub
>
> The first OnTime procedure runs fine, but when I add the optional
> "Schedule", I get an error "Method OnTime of object _Application failed. When
> I select Help from the Debugger, I get a blank page. VBA help is installed.
>
> Can anyone help?

[Sam]

Thanks Chip. This was much more helpful than online help.

"Chip Pearson" wrote:

> Sam,
>
> To use the Schedule:=False argument to cancel an OnTime
> procedure, you must provide OnTime with the *exact* time that the
> event was scheduled. See www.cpearson.com/excel/ontime.htm for
> details and example code.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
> "Sam" <[email protected]> wrote in message
> news:[email protected]...
> >I am testing a simple OnTime procedure and receive an error 1004
> >upon
> > execution. Entire code is:
> > Sub SetAlarm()
> > Application.OnTime TimeValue("10:35:00 am"), "RunTest"
> > End Sub
> >
> > Sub RunTest()
> > Range("A50").End(xlUp).Offset(1, 0).Value = Date
> > End Sub
> >
> > Sub SetAnotherAlarm()
> > Application.OnTime EarliestTime:=TimeValue("10:48:00 am"),
> > Procedure:="RunTest", Schedule:=False
> > End Sub
> >
> > The first OnTime procedure runs fine, but when I add the
> > optional
> > "Schedule", I get an error "Method OnTime of object
> > _Application failed. When
> > I select Help from the Debugger, I get a blank page. VBA help
> > is installed.
> >
> > Can anyone help?
>
>
>