Application.OnTime

[rhhince]

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.

 

[Claus Busch]

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.

 

[joeu2004]

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)