Solved: Schedule a macro

[cactus]

Hi,

In a book that will be permanently open, I need to schedule a macro to run each 30 minutes between two specifics hours (6 am and 11 pm).
Thanks and sorry for my english.

Cactus.-

[malik641]

Hi Cactus,

I would check out Chip Pearson's website to learn about the .OnTime method.

I believe the following code will do what you ask. All you have to do is remove the comments from the following lines:
[VBA]'Application.OnTime EarliestTime:=dblRunWhen, _
' Procedure:=strRunWhat, _
' Schedule:=True[/VBA]

And you have to change the variable strRunWhat to whatever the procedure name is that you want to execute.

Here is the code:
[VBA]Option Explicit
Option Private Module

Const strRunWhat = "The_Procedure_Name"

Public Sub SetUpMacroSchedule()
' Sets up a schedule for a specific macro(s) to be run
Dim i As Long
Dim dblCurrentTimeInLoop As Double ' To stop the loop at 11:00 PM
Dim dblRunWhen() As Double

' Set i=1 to start the array counter
i = 1

' Start time at 6:00 AM (relative to the Computer System Time)
ReDim dblRunWhen(0)
dblRunWhen(0) = DateSerial(Year(Now()), Month(Now()), Day(Now())) _
+ TimeSerial(6, 0, 0)

dblCurrentTimeInLoop = dblRunWhen(0)
' FOR TESTING PURPOSES
Debug.Print DateSerial(Year(dblRunWhen(0)), Month(dblRunWhen(0)), Day(dblRunWhen(0))) _
+ TimeSerial(Hour(dblRunWhen(0)), Minute(dblRunWhen(0)), Second(dblRunWhen(0)))

' Create a loop to set up the array of timed tasks.
' Loop's condition is to continue looping until the time
' of dblCurrentTimeInLoop is greater than 11:00 PM
Do While (TimeSerial(Hour(dblCurrentTimeInLoop), _
Minute(dblCurrentTimeInLoop), 0) < TimeSerial(23, 0, 0))
ReDim Preserve dblRunWhen(i)
' Set the next dblRunWhen to the next half-hour
dblRunWhen(i) = dblRunWhen(i - 1) + TimeSerial(0, 30, 0)

' FOR TESTING PURPOSES
Debug.Print DateSerial(Year(dblRunWhen(i)), Month(dblRunWhen(i)), Day(dblRunWhen(i))) _
+ TimeSerial(Hour(dblRunWhen(i)), Minute(dblRunWhen(i)), Second(dblRunWhen(i)))

'Application.OnTime EarliestTime:=dblRunWhen, _
' Procedure:=strRunWhat, _
' Schedule:=True

dblCurrentTimeInLoop = dblRunWhen(i)

' Increment i
i = i + 1

Loop

End Sub[/VBA]
As it is now, it will print the schedule in the Immediate window for you (just to be sure that the code works correctly). Test it and let us know if it produces the correct schedule for you

Good luck!

[cactus]

It works perfectly!!!

Thank you very much

[malik641]

Your welcome

And welcome to VBAX!

[malik641]

Oh yeah. Don't forget to mark your thread "Solved" by using the thread tools inside the thread

[Zack Barresse]

One large caveat of this is you must have the file open. As you say the file will always be open, this could lead to problems. Such as what if the file closes? If this would ever be an issue for you, setup a vbs file to run with Windows Scheduler (assuming you have a Windows OS) at your desired times. An example of this can be found at this running thread ...

http://forums.techguy.org/business-a...xcel-help.html

HTH

[Bob Phillips]

If it isn't open, it will open when it's time is due, as long as Excel is running.

[cactus]

Hi,

I`ll try to use the Windows Scheduler and an AutoOpen maybe (?)...It looks easiest and strongest for a beginer like me.
Anyway the solution porpoused by malik641 was very useful.

Thanks again

[Zack Barresse]

If it isn't open, it will open when it's time is due, as long as Excel is running.

Hence the possible problem.

If it is a non-issue to the OP, kudos, but it needs to be addressed.