Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Sub automatically on periodic basis
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 -- Paul Kraemer |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Sub automatically on periodic basis
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"), Procedu="my_Procedure" End Sub -- Cheers, Ryan "Paul Kraemer" wrote: 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 -- Paul Kraemer |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run Sub automatically on periodic basis
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, _ Procedu="my_Procedure" End Sub Sub StopOnTime Application.OnTime earliesttime:=RunWhen, _ procedu="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] On Mon, 1 Feb 2010 12:03:01 -0800, Ryan H wrote: 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"), Procedu="my_Procedure" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I automatically reset numbers to 0 on a daily basis? | Excel Discussion (Misc queries) | |||
Automatically create list in different sheet on basis of other lis | Excel Worksheet Functions | |||
How do I update graphs automatically with periodic data updates | Charts and Charting in Excel | |||
Summing periodic sales on a rolling basis | Excel Worksheet Functions | |||
Periodic Calculating | Excel Worksheet Functions |