Run Sub automatically on periodic basis

[Paul Kraemer]

Hi,

I am using Excel 2007. I have written a Subroutine that I want to run
automatically once every thirty seconds without any user interaction. Can
anyone tell me how I can set this up?

Thanks in advance,
Paul

 

[Ryan H]

This code will run your procedure when the workbook has been opened. At the
end of your code the OnTime event will schedule another run of your code 30
seconds later. Thus, it will continue to loop until you close the workbook.
Hope this helps! If so, let me know, click "YES" below.

Private Sub Workbook_Open()
Call my_Procedure
End Sub

Sub my_Procedure()

' your code here

Application.OnTime EarliestTime:=Now + TimeValue("00:00:30"),
Procedure:="my_Procedure"

End Sub

 

[Chip Pearson]

You provided no code for stopping the proc from rescheduling itself.
To end an OnTime event, you must provide it the EXACT time that it is
scheduled to run. Since you use Now+TimeValue(), you have no way of
getting the scheduled run time. Instead, you should save the time
value in a module-scoped variable and pass the value of that variable
to OnTime. E.g.

Dim RunWhen As Double
Sub my_Procedure()
' your code here
RunWhen = Now + TimeSerial(0,0,30)
application.ontime EarliestTime:=RunWhen, _
Procedure:="my_Procedure"
End Sub

Sub StopOnTime
Application.OnTime earliesttime:=RunWhen, _
procedure:="my_Procedure", schedule:=False
End Sub

See www.cpearson.com/Excel/OnTime.aspx for more detail about working
with OnTime and Windows timers.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]