Running a Macro periodically between Time A and Time B

[Kfletch]

Thanks in advance to anyone who can help me with this.

I have a simple excel macro written and want it to run every so often
(5 min / 10 min / 1 hour not sure yet) between the hours of 8:35:00 am
and 3:05:00 pm. I have been digging through related topics regarding
the OnTime method (here.. http://www.cpearson.com/excel/ontime.htm and
here.. http://www.ozgrid.com/Excel/run-macro-on-time.htm as well as
many more) and cannot really get my head around the easiest way to do
this.

Most of my confusion comes from where to write the code whether it be
in my current macro module, or in new modules / where and how the
sub's and sub of sub's go. Also I have trouble determining what is
actual code and what is a field I must define from much of the
information that is out there.

It would be much appreciated if anyone could help an amateur.

 

[Dave Peterson]

I would take another look at Chip's instructions and code:
http://www.cpearson.com/excel/OnTime.aspx

Then if you have specific questions about that code, post back. (Read through
the "stopping a time" section. Ignore the stuff after that.)

And for Chip's code, you can put all that stuff in a single module.

 

[Kfletch]

I would take another look at Chip's instructions and code:http://www.cpearson.com/excel/OnTime.aspx

Then if you have specific questions about that code, post back.  (Read through
the "stopping a time" section.  Ignore the stuff after that.)

And for Chip's code, you can put all that stuff in a single module.

OK, you are correct, I made my way into the process a bit and
determined that several of my questions were easily answered.

Now, for the actual code question. In this section for starting (and
stopping) the timer here...

Sub StartTimer()
RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True
End Sub

I am not sure which portions of this are actual code that needs to
remain, and which portions I need to complete, also what format the
numbers need to be in.
So If I want the macro to run at 8:35:00 am and stop at 15:05:00 pm
and have it run every 10 minutes between those times, would this
complete the needs of the timer

Sub StartTimer()
RunWhen = Now + TimeSerial(0,0,c600.00)
Application.OnTime EarliestTime:=08:35:00, Procedure:=c"My
Macro Name", _
Schedule:=True
End Sub

 

[Kfletch]

OK, you are correct, I made my way into the process a bit and
determined that several of my questions were easily answered.

Now, for the actual code question. In this section for starting (and
stopping) the timer here...

Sub StartTimer()
    RunWhen = Now + TimeSerial(0,0,cRunIntervalSeconds)
    Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
        Schedule:=True
End Sub

I am not sure which portions of this are actual code that needs to
remain, and which portions I need to complete, also what format the
numbers need to be in.
So If I want the macro to run at 8:35:00 am and stop at 15:05:00 pm
and have it run every 10 minutes between those times, would this
complete the needs of the timer

   Sub StartTimer()
       RunWhen = Now + TimeSerial(0,0,c600.00)
       Application.OnTime EarliestTime:=08:35:00, Procedure:=c"My
Macro Name", _
           Schedule:=True
   End Sub- Hide quoted text -

- Show quoted text -

After a little more work I have been able to get the macro running,
however I cannot determine where I input the time to start the timer,
and where to stop the timer. See full code below.

Public RunWhen As Double
Public Const cRunIntervalSeconds = 60 ' one minute
Public Const cRunWhat = "SpreadRecordMacro1"
_____________________________________________
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=True

End Sub
____________________________________________

Sub SpreadRecordMacro1()
' SpreadRecordMacro1 Macro
' '
"My Macro" .............

StartTimer

End Sub
___________________________________________

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat, _
Schedule:=False
End Sub

I have tried to input a time to start, and a time to stop. I must be
inputing in improperly or in the incorrect place. Any advice would be
much appreciated.

 

[Dave Peterson]

Chip's code is pretty nice.

This line:
Public Const cRunIntervalSeconds = 60
is how you can change the interval.

since you want 600 seconds (10 minutes), you can use:
Public Const cRunIntervalSeconds = 600 '60*10


=========
This is untested, but it did compile.

Option Explicit
'So If I want the macro to run at 8:35:00 am and stop at 15:05:00 pm
'and have it run every 10 minutes between those times, would this
'complete the needs of the timer
Public RunWhen As Double
Public Const cRunIntervalSeconds As Long = 600 '60 seconds * 10 minutes
Public Const cRunWhat As String = "SpreadRecordMacro1"
Public Const BeginTime As Date = #8:35:00 AM#
Public Const FinishTime As Date = #3:05:00 PM#
Sub Auto_Open()
If Time < BeginTime Then
'wait until that time
Application.OnTime earliesttime:=BeginTime, _
procedure:=cRunWhat
Else
'just start it right now????
Application.Run cRunWhat
End If
End Sub
Sub Auto_Close()
'if you're closing the workbook, then
'stop the timer from reopening your workbook
'and running the macro!!
Call StopTimer
End Sub
Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
Schedule:=True
End Sub
Sub SpreadRecordMacro1()
MsgBox "My Macro could go here"

'600/60/24 is 10 minutes
'so check to see if it's too late to schedule the next
'run
If Time > (FinishTime - (cRunIntervalSeconds / 60 / 24)) Then
'don't start it again.
Else
StartTimer
End If
End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
Schedule:=False
End Sub

==============
There's actually a small design flaw/bug in this code. If your times got close
to midnight, then the comparisons could fail when you when want them to.

But I bet that won't affect you. If you do start to work long hours, I'll leave
it to you to fix the problem!

 

[Kfletch]

Chip's code is pretty nice.

This line:
Public Const cRunIntervalSeconds = 60
is how you can change the interval.

since you want 600 seconds (10 minutes), you can use:
Public Const cRunIntervalSeconds = 600 '60*10

=========
This is untested, but it did compile.

Option Explicit
'So If I want the macro to run at 8:35:00 am and stop at 15:05:00 pm
'and have it run every 10 minutes between those times, would this
'complete the needs of the timer
Public RunWhen As Double
Public Const cRunIntervalSeconds As Long = 600 '60 seconds * 10 minutes
Public Const cRunWhat As String = "SpreadRecordMacro1"
Public Const BeginTime As Date = #8:35:00 AM#
Public Const FinishTime As Date = #3:05:00 PM#
Sub Auto_Open()
     If Time < BeginTime Then
         'wait until that time
         Application.OnTime earliesttime:=BeginTime, _
             procedure:=cRunWhat
     Else
         'just start it right now????
         Application.Run cRunWhat
     End If
End Sub
Sub Auto_Close()
     'if you're closing the workbook, then
     'stop the timer from reopening your workbook
     'and running the macro!!
     Call StopTimer
End Sub
Sub StartTimer()
     RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
     Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
         Schedule:=True
End Sub
Sub SpreadRecordMacro1()
     MsgBox "My Macro could go here"

     '600/60/24 is 10 minutes
     'so check to see if it's too late to schedule the next
     'run
     If Time > (FinishTime - (cRunIntervalSeconds / 60 / 24)) Then
         'don't start it again.
     Else
         StartTimer
     End If
End Sub
Sub StopTimer()
     On Error Resume Next
     Application.OnTime earliesttime:=RunWhen, procedure:=cRunWhat, _
         Schedule:=False
End Sub

==============
There's actually a small design flaw/bug in this code.  If your times got close
to midnight, then the comparisons could fail when you when want them to.

But I bet that won't affect you.  If you do start to work long hours, I'll leave
it to you to fix the problem!

Dave;

I am very appreciative of your help. Your code is working wonderfully
for now and I will watch it a few days to confirm that it is stopping
and starting properly.

The only problem i am running into now is that I run about 5 different
excel models on my screens during the day. I switch back and forth
between them and just realized that the "my macro" (a simple copy :
Paste Special command) is running in whatever excel model I happen to
be in. What is the exact code and procedure (specifically for this
series of sub and sub macros) to ensure that the macro only runs in
the workbook I have the macro built in? In looking at the current
code, it would seem the time variables would not need to be workbook
specific, just "my Macro". I assume I would just need to add a line
before the first line in the "my macro" code that essentially states
"go to - a specific file, run the macro". I dont mind if it forces the
workbook I am working in to become inactive since, from my research,
having it work in the background on an inactive workbook seems
troublesome.

Best Regards

 

[Dave Peterson]

I'm confused about your question.

If the macros in the correct workbook are not running, then make sure you use
nice unique names for each of the macros. It'll make running the correct one
easier.

If you mean that the correct macro runs, but it runs against the wrong
worksheet(s), then make sure you qualify the worksheets.

Instead of using:
worksheets("Sheet1").range("A1:b99").copy
worksheets("Sheet2").range("A1").pastespecial _
paste:=xlpastevalues

make sure you refer to them with something like:

ThisWorkbook.worksheets("Sheet1").range("A1:b99").copy
ThisWorkbook.worksheets("Sheet2").range("A1").pastespecial _
paste:=xlpastevalues

ThisWorkbook will refer to the workbook that owns the code.



On 10/01/2010 09:12, Kfletch wrote:

 

[Kfletch]

I'm confused about your question.

If the macros in the correct workbook are not running, then make sure youuse
nice unique names for each of the macros.  It'll make running the correct one
easier.

If you mean that the correct macro runs, but it runs against the wrong
worksheet(s), then make sure you qualify the worksheets.

Instead of using:
worksheets("Sheet1").range("A1:b99").copy
worksheets("Sheet2").range("A1").pastespecial _
    paste:=xlpastevalues

make sure you refer to them with something like:

ThisWorkbook.worksheets("Sheet1").range("A1:b99").copy
ThisWorkbook.worksheets("Sheet2").range("A1").pastespecial _
    paste:=xlpastevalues

ThisWorkbook will refer to the workbook that owns the code.

On 10/01/2010 09:12, Kfletch wrote:
<<snipped>>

Your second part answered my question. I had to have the proper
workbook go "active" prior to running the macro. Everything seems to
be working fine now. Thank you very much for your help!