Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Application.OnTime
I took my spreadsheet and went to view code and inserted this to run a macro every 15 seconds.
Application.OnTime Now + TimeValue("00:00:15"), "Hourly_update" I get Compile error: Invalid outside procedure. Can anyone explain why that is? I am trying to run the macro every 15 seconds. Thanks. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Application.OnTime
Hi,
Am Tue, 13 Aug 2013 01:20:13 -0700 (PDT) schrieb rhhince: Compile error: Invalid outside procedure. code has to be in a procedure, e.g. a sub or a worksheet event try: Sub OnTime Application.OnTime Now + TimeValue("00:00:15"), "Hourly_update" End Sub and run the macro manually. Or try Private Sub Workbook_Open() Application.OnTime Now + TimeValue("00:00:15"), "Hourly_update" End Sub Why do you want to run the HOURLY update every 15 seconds? Regards Claus B. -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Application.OnTime
"rhhince" wrote:
I took my spreadsheet and went to view code and inserted this to run a macro every 15 seconds. Application.OnTime Now + TimeValue("00:00:15"), "Hourly_update" I get Compile error: Invalid outside procedure. Can anyone explain why that is? Several issue to consider. 1. All code must be __inside__ a procedure, a Sub(routine) in this case. That is, __between__ the "Sub" and "End Sub" lines. Alternatively, you could execute the Application.OnTime statement in the Immediate Window (press ctrl-G). But that is highly unusual, and it is not recommended. 2. As written, the procedure "Hourly_update" [sic] must be in a normal module, created by clicking on Insert, then Module, not in a worksheet (object) module, which is where you go when you click on View Code. If you want to put "Hourly_update" [sic] into a worksheet module, you must qualify the procedure name with the worksheet __object__ name, which is often __not__ the worksheet name. For example, the worksheet name might be "summary", but the object name might be Sheet3. Initiate the OnTime event with the following statement: Application.OnTime Now + TimeValue("00:00:15"), "Sheet3.Hourly_update" You can see worksheet object names in the Project Explorer. Press ctrl-R, and double-click on Microsoft Excel Objects. 3. It would be prudent to put Now+TimeValue("00:00:15") into a global variable. This will allow you to cancel the OnTime event later, a prudent feature to have for recurring events. For example: Dim nextTime As Double Sub startit() nextTime = Now + TimeSerial(0, 0, 15) Application.OnTime nextTime, "doit" End Sub Sub doit() nextTime = nextTime + TimeSerial(0, 0, 15) Application.OnTime nextTime, "doit" ' ... the operation ... End Sub Sub stopit() Application.OnTime nextTime, "doit", , False End Sub The coding above also avoids "time skew", which is caused by the fact that the execution of "doit" might be delayed due to other activity on the computer. There are applications when you might prefer the "time skew". In that case, change the statement in "doit" to: nextTime = Now + TimeSerial(0, 0, 15) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using application.ontime | Excel Programming | |||
Help with Application.OnTime | Excel Programming | |||
application.ontime | Excel Programming | |||
Application.OnTIme | Excel Programming | |||
Application.OnTime and Visual C++ | Excel Programming |