Hi all,

Am using Excel 2010. I am trying to run a macro on just one day only,
anytime throughout the day starting from 09:30 onwards. But not run it
if the day has changed.

I have the following code in the Workbook_Open section:
RunTimeFirst = DateSerial(2012, 12, 9) + TimeSerial(9, 30, 0)
LastRun = RunFirstTime + 48600
Application.OnTime EarliestTime:=RunTimeFirst, _
Procedu="Birthday_Message", LatestTime:=LastRun, Schedule:=True

The problem I'm having is understanding the LatestTime parameter. The
help file states:
"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."

Does LatestTime also stop the macro from running if LatestTime has
passed, or just pause for time while Excel is not in ready mode?
Is my LastRun declaration correct. Is the 48600 seconds like the help
file says, or another value? (days?)

When I run this now the macro will run everyday later than the
RunTimeFirst date, and seems to ignore LastRun time.

I know I can add an If statement to check the date:
If Now DateSerial(2012, 12, 10) Then Exit Sub

This will work, but I just wanted to know how LatestTime worked.

Thanks in advance,
edul

Given that you want this to run conditionally, the If construct is
necessary. That said, the date of the day you want `Birthday_Message`to
run is all that`s required...

pseudo code:
If Date() = dteRunDate Then Call Birthday_Message

...where dteRunDate is a module level variable of Type *Date*...

Const dteRunDate As Date = "12/11/2012" '//edit to suit
===


As for the OnTime function:
The default for the *Schedule* arg is *True* and so needs only to be
specified if *False* is preferred.

The online help explains how each arg works. LatestTime is conditional
on Excel`s current mode.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On 11/12/2012 16:38, GS wrote:
Given that you want this to run conditionally, the If construct is
necessary. That said, the date of the day you want `Birthday_Message`to
run is all that`s required...

pseudo code:


===


As for the OnTime function:
The default for the *Schedule* arg is *True* and so needs only to be
specified if *False* is preferred.

The online help explains how each arg works. LatestTime is conditional
on Excel`s current mode.

Hi Garry,

Thanks for the reply.
If I only use Date then the macro will run prior to time specified, and
also this workbook is left open for days at a time, so there will be no
"trigger" for the procedure.

I have read the help on LatestTime, agree with you it's conditional on
Excel's current mode, but is that all. My question still stands.
Does LatestTime also stop the procedure from running if LatestTime has
passed, or just pause for time while Excel is not in ready mode?
There is no help available on this. Testing this I guess it only stops
the macro from running while Excel is not in ready mode, but could
someone confirm.

Have adapted my code to currently looks like this:

Private Sub Workbook_Open()
' updates links without prompt
ActiveWorkbook.UpdateLinks = xlUpdateLinksAlways

'Calls Birthday_Message macro if date before 20/12
If Now = DateSerial(2012, 12, 17) Then Exit Sub

RunTimeFirst = DateSerial(2012, 12, 16) + TimeSerial(8, 30, 0)

Application.OnTime EarliestTime:=RunTimeFirst, _
Procedu="Birthday_Message"

End Sub

Public RunTimeFirst As Date, LastRun As Date, RunTime3 As Date

Sub Birthday_Message()
MsgBox "MESSAGE"
LastRun = DateSerial(2012, 12, 16) + TimeSerial(12, 0, 0)
RunTime3 = Now + TimeValue("00:20:00")

Application.OnTime EarliestTime:=RunTime3, Procedu="Birthday_Message"

Application.OnTime EarliestTime:=LastRun, _
Procedu="CancelOnTime"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Call CancelOnTime
On Error GoTo 0
End Sub

Sub CancelOnTime()
On Error Resume Next
Application.OnTime EarliestTime:=RunTimeFirst,
Procedu="Birthday_Message", _
Schedule:=False
Application.OnTime EarliestTime:=RunTime3,
Procedu="Birthday_Message", _
Schedule:=False
On Error GoTo 0
End Sub


This runs the process on 16th Dec at 08:30 if the sheet is open, until
12:00, every 20 mins. Times and Dates can be changed to suit.
This is the only way I can get the macro to start/stop on times.

Hopefully, someone with more experience with this than I will chip in.
My only use of OnTime matches the example in Help. I know that J-Walk
has an "alarm clock" example available which might shed some light on
using the function for a preset/specified time...

http://spreadsheetpage.com/

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion