Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ontime code
Sub AutoRunMe() Application.OnTime Now + TimeSerial(0, 0, 10), "MyMacro" End Sub Sub MyMacro() MsgBox Now Call AutoRunMe End Sub That code isn't a good idea, because it doesn't allow you to cancel the pending on time event. The timer rescheduling will turn off only if you completely quit Excel. Closing the workbook isn't enough -Excel will reopen it when the timer pops. Instead, create a module level variable and assign to it the value when the code should run: Public RunWhen As Double Sub RunMe() ' code RunWhen = Now + TimeSerial(0, 0, 10) Application.OnTime RunWhen, "RunMe", , True End Sub To cancel a timer, you must specify the *exact* time that it is schedule to pop. You can do this only if you have stored the time value in a module-scope variable. Sub StopTimer() Application.OnTime RunWhen, "RunMe", , False End Sub I have extensive notes about OnTime at http://www.cpearson.com/excel/OnTime.aspx Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Thu, 16 Apr 2009 09:50:02 -0700, Jacob Skaria wrote: Hi Steve Insert a module and paste the below procedures and try. Sub AutoRunMe() Application.OnTime Now + TimeSerial(0, 0, 10), "MyMacro" End Sub Sub MyMacro() MsgBox Now Call AutoRunMe End Sub If this post helps click Yes --------------- Jacob Skaria "Steve" wrote: Morning all. I'm interested in doing an "autorun" macro that will run every few minutes, to every couple of hours. I've looked around here on the newsgroup, and the various forums, and found some examples, but in the application of them, I don't seem to get what I want. See code.... Sub AutoRunMe() application.ontime now + timeserial(0, 0, 10), "MyMacro" end sub In my reading, it states that the above code will run every 10 seconds, following an initial start time of "now." If however I do this: application.ontime timeserial( 0, 0, 10), "MyMacro" it'll only run at 10 seconds past midnight. I also tried now + timevalue("00:00:10"); it's not repeating every 10 seconds either. What I'm finding is that the first one does not run every ten seconds-- I tried it just to make sure it'll do what I need-- and I cannot find anything else that will allow me to run the macro every few minutes or hours, as I decide. I realize that I don't entirely understand it, so I wanted to ask-- how do I create a macro that'll run every few minutes or so? Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ontime code | Excel Programming | |||
Find %ontime & SUMIF ontime ie: find matching sets within Range... | Excel Worksheet Functions | |||
.ontime | Excel Discussion (Misc queries) | |||
OnTime code error "can't execute code in break mode" | Excel Programming | |||
about ontime | Excel Programming |