Application.OnTime

Hi mcarter973,

Simply put the following code in the ThisWorkbook event code module:

Private Sub Workbook_Open()
The_Sub
End Sub

This will run The_Sub when the workbook opens. To get to the ThisWorkbook event code module from Excel, right-click on the Excel icon at the left end of the Worksheet Menu Bar and select View Code. Paste the code into this code pane.

Thanks Damon - I have a follow-up ? - why does"The_Sub" run when I open the workbook? - shouldn't the code be triggered by the timer (starting at 12:00AM and one hour thereafter)?

Thanks again.

Mike

Hi Mike,

The_Sub runs because the Workbook_Open procedure always runs when the workbook opens, because that is what the Workbook_Open event is for. In this case Workbook_Open immediately calls The_Sub. But because The_Sub calls StartTimer, and StartTimer schedules The_Sub to run again in an hour, The_Sub will run again every hour after the workbook is opened.

Damon

Damon -

Thanks again for your help. Instead of using an interval, I would like to print a file at staggered points throughout the day.

I tested the code this morning - at 3:00AM it printed every few seconds for two minutes and at 5:00AM it printed every few seconds for four minutes. What am I doing wrong?


Public RunWhen As Double
Public RunWhen2 As Double
Public RunWhen3 As Double
Public RunWhen4 As Double
Public RunWhen5 As Double
Public RunWhen6 As Double
Public Const cRunWhat = "The_Sub"

Sub StartTimer()
RunWhen = TimeValue("3:00 AM")
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True
RunWhen2 = TimeValue("5:00 AM")
Application.OnTime earliesttime:=RunWhen2, procedure:=cRunWhat, _
schedule:=True
RunWhen3 = TimeValue("11:00 AM")
Application.OnTime earliesttime:=RunWhen3, procedure:=cRunWhat, _
schedule:=True
RunWhen4 = TimeValue("12:00 PM")
Application.OnTime earliesttime:=RunWhen4, procedure:=cRunWhat, _
schedule:=True
RunWhen5 = TimeValue("3:00 PM")
Application.OnTime earliesttime:=RunWhen5, procedure:=cRunWhat, _
schedule:=True
RunWhen6 = TimeValue("5:00 PM")
Application.OnTime earliesttime:=RunWhen6, procedure:=cRunWhat, _
schedule:=True

End Sub

Sub The_Sub()
If Cells(3, 1) = 0 Then
Exit Sub
End If
Sheets("Sheet1").Select
Columns("B:B").EntireColumn.AutoFit
ActiveWindow.SelectedSheets.PrintOut Copies:=1
StartTimer
Application.DisplayAlerts = False
ActiveWorkbook.Save
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, _
procedure:=cRunWhat, schedule:=False
Application.OnTime earliesttime:=RunWhen2, _
procedure:=cRunWhat, schedule:=False
Application.OnTime earliesttime:=RunWhen3, _
procedure:=cRunWhat, schedule:=False
Application.OnTime earliesttime:=RunWhen4, _
procedure:=cRunWhat, schedule:=False
Application.OnTime earliesttime:=RunWhen5, _
procedure:=cRunWhat, schedule:=False
Application.OnTime earliesttime:=RunWhen6, _
procedure:=cRunWhat, schedule:=False

End Sub

Hi again Mike,

I don't know why it ran at 3:00 and 5:00. It looks to me like your code should run immediately rather than at the scheduled times. The reason is this:

The OnTime method says it wants a time input, but what it really wants is a "date-time", i.e., you must specify what date you want it to run as well. You could have specified the date-time as, for example,

RunWhen = CDate("10/23/2003 3:00 AM")

but this code would only work on the specific date indicated. To write the code so that it will always assume the current day:

RunWhen = Date() + TimeValue("3:00 AM")

where the Date function is used to provide the current date.

It would also be best to declare RunWhen (and RunWhen2, 3, etc.) as a Date data type, which is what it really is:

Public RunWhen As Date

This should clear up the problem.

Damon

_______________________________________________________

PS. incidentally, the code

Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
schedule:=True

could be shortened to

Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat

because the default value of the shedule argument is True.

How do you do in VBA the same thing that Application.OnTime does in terms of running a procedure?

i.e. I want to create a subroutine which takes the name of another subroutine as an argument and then runs it.

?

Application.Run "Macro1"

or Call Macro1