Hi All,
I have a macro that I would like to start at 8:30am and then repeat every 15
seconds, and stop at 3pm. I did look at cperson.com but I am unsure of how
to write this specifically. Thanks,

Hi,

Try this one, as your req. Place in standard module

Option Explicit
Dim add

Sub Scheduled()
Application.OnTime TimeValue("08:" & Format(add, "00") & ":00"),
"Procedure"
End Sub

Sub Procedure()
add = add + 1
If Format(Now, "m") = 3 Then End
Scheduled
End Sub

Sub test()
add = 1
Scheduled
End Sub


'Then run test

--
Regards,

Halim



"andy" wrote:

Hi All,
I have a macro that I would like to start at 8:30am and then repeat every 15
seconds, and stop at 3pm. I did look at cperson.com but I am unsure of how
to write this specifically. Thanks,

Thanks Halim,
I am fairly new at VBA. What part of the code says to repeat the macro
every 15 secs? I might like to change this varible in the future. Thanks!

"Halim" wrote:

Hi,

Try this one, as your req. Place in standard module

Option Explicit
Dim add

Sub Scheduled()
Application.OnTime TimeValue("08:" & Format(add, "00") & ":00"),
"Procedure"
End Sub

Sub Procedure()
add = add + 1
If Format(Now, "m") = 3 Then End
Scheduled
End Sub

Sub test()
add = 1
Scheduled
End Sub


'Then run test

--
Regards,

Halim



"andy" wrote:

Hi All,
I have a macro that I would like to start at 8:30am and then repeat every 15
seconds, and stop at 3pm. I did look at cperson.com but I am unsure of how
to write this specifically. Thanks,

Chip Pearson explains how to use application.ontime:
http://www.cpearson.com/excel/ontime.htm

andy wrote:

Hi All,
I have a macro that I would like to start at 8:30am and then repeat every 15
seconds, and stop at 3pm. I did look at cperson.com but I am unsure of how
to write this specifically. Thanks,

--

Dave Peterson

Thanks Dave. I have very limited experience with VBA. I have been on Chip
Pearson's site before I wrote this question and I don't fully understand how
to write this macro. (I am trying to teach myself VBA as we speak.) Could
you please help clarify how to start a macro at 830am and then repeat the
macro every 15 secs and end it at 3pm? Thanks!

"Dave Peterson" wrote:

Chip Pearson explains how to use application.ontime:
http://www.cpearson.com/excel/ontime.htm

andy wrote:

Hi All,
I have a macro that I would like to start at 8:30am and then repeat every 15
seconds, and stop at 3pm. I did look at cperson.com but I am unsure of how
to write this specifically. Thanks,

--

Dave Peterson

This assumes that you don't open this workbook until after 8:30 AM:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 15
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run
Dim FirstTime As Boolean

Sub StartTimer()
If FirstTime Then
RunWhen = TimeSerial(8, 30, 0)
Else
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
End If
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat,
Schedule:=True
End Sub

Sub The_Sub()

MsgBox "hi " & Time
'''''''''''''''''
' Your Code Here
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(15, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub

Sub StopTimer()
'still useful for testing to stop any scheduled macro
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedu=cRunWhat,
Schedule:=False
End Sub

Sub Auto_Open()
FirstTime = True
If Time TimeSerial(8, 30, 0) Then
FirstTime = False
End If
Call StartTimer
FirstTime = False
End Sub

This does assume that you close excel before midnight and reopen this file the
next day.


andy wrote:

Thanks Dave. I have very limited experience with VBA. I have been on Chip
Pearson's site before I wrote this question and I don't fully understand how
to write this macro. (I am trying to teach myself VBA as we speak.) Could
you please help clarify how to start a macro at 830am and then repeat the
macro every 15 secs and end it at 3pm? Thanks!

"Dave Peterson" wrote:

Chip Pearson explains how to use application.ontime:
http://www.cpearson.com/excel/ontime.htm

andy wrote:

Hi All,
I have a macro that I would like to start at 8:30am and then repeat every 15
seconds, and stop at 3pm. I did look at cperson.com but I am unsure of how
to write this specifically. Thanks,

--

Dave Peterson


--

Dave Peterson

Ignore that first note--I changed the code but forgot to delete the note. This
fixes a word wrap problem, too:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 15
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run
Dim FirstTime As Boolean
Sub StartTimer()
If FirstTime Then
RunWhen = TimeSerial(8, 30, 0)
Else
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
End If
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=True
End Sub
Sub The_Sub()

MsgBox "hi " & Time
'''''''''''''''''
' Your Code Here
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(15, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub
Sub StopTimer()
'useful for testing to stop any scheduled macro
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=False
End Sub
Sub Auto_Open()
FirstTime = True
If Time TimeSerial(18, 30, 0) Then
FirstTime = False
End If
Call StartTimer
FirstTime = False
End Sub

But this still applies:

This does assume that you close excel before midnight and reopen this file the
next day.



Dave Peterson wrote:
<<snipped
--

Dave Peterson

Excellent! It works perfectly!
Clarification: I should close this file by midnight each night. And, if I
open this file before 830am will it still work the same? As of now, I open
this file before 830am so that it runs while I am at work. Thanks!

"Dave Peterson" wrote:

Ignore that first note--I changed the code but forgot to delete the note. This
fixes a word wrap problem, too:

Option Explicit
Public RunWhen As Double
Public Const cRunIntervalSeconds = 15
Public Const cRunWhat = "The_Sub" ' the name of the procedure to run
Dim FirstTime As Boolean
Sub StartTimer()
If FirstTime Then
RunWhen = TimeSerial(8, 30, 0)
Else
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
End If
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=True
End Sub
Sub The_Sub()

MsgBox "hi " & Time
'''''''''''''''''
' Your Code Here
'''''''''''''''''
' Call StartTimer to schedule the procedure again
If Time TimeSerial(15, 0, 0) Then
'do nothing
Else
StartTimer
End If
End Sub
Sub StopTimer()
'useful for testing to stop any scheduled macro
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, _
Procedu=cRunWhat, Schedule:=False
End Sub
Sub Auto_Open()
FirstTime = True
If Time TimeSerial(18, 30, 0) Then
FirstTime = False
End If
Call StartTimer
FirstTime = False
End Sub

But this still applies:

This does assume that you close excel before midnight and reopen this file the
next day.



Dave Peterson wrote:
<<snipped
--

Dave Peterson