Application.OnTime running only once?

[sark666]

I have the following sub in the ThisWorkbook section:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("03:00:00"), "START_procedure"
    
End Sub

I want it to run every day at 3 am. But it's running only once.

Hmm, I think I see my problem, under the thisWorkbook it's under workbook and Open so obviously it's only excuting once upon opening. Should I just have it as (General) (Declarations) ?

Does an application.OnTime macro even need to be in ThisWorkbook section? I thought I had read in a tutorial to set it up like that but I may be mistaken.

 

[VoG]

This should help you http://www.ozgrid.com/Excel/run-macro-on-time.htm

 

[prajul89]

So what are you saying? the macro does shoot on 3AM

 

[sark666]

I'm saying it runs only once and not the next day as I left it open the entire time. If I shut the excel file down and re-open then it will run again. I want it to run daily even if I leave it open all week and even if the sheet is not active (ie. it's minimized for example). But I'll check out that link.

Ok that's actually the tutorial I originally followed. It says:

This Method is what we can use to achieve the automatically running of Excel Macros. Let's suppose we have a macro that we wish to Run each day at 15:00 (3:00 PM). The first problem will be how to kick-off the OnTime Method. This we can do via the Workbook Open Event. The fastest way to get to the Private Module of the Workbook Object (ThisWorkbook) is to right click on the Excel icon next to "File" and select "View Code"

Private Sub Workbook_Open()
Application.OnTime TimeValue("15:00:00"), "MyMacro"End Sub</PRE>

It says we can do this via the workbook open event, which I am using but doesn't that mean it only runs once upon opening? Cause that's what is happening for me, so I thought it was incorrect to have it as a Workbook Open event. Unless my macro is halting for some other reason. When I come back to work I see no error, it just seems like it hasn't run a second time. I can put in some debugging to see if it is in fact running, but again, if I close/re-open then it again runs at 3:00 am but not the next day.

 

[VoG]

Basically, your Start_procedure macro also has to invoke OnTime.

 

[prajul89]

 Application.OnTime TimeValue("03:00:00"), "START_procedure"

put this line in START_procedure also!

 

[sark666]

Ok I will try that. And yes it actually says that in the tutorial:

Sub MyMacro() Application.OnTime TimeValue("15:00:00"), "MyMacro"'YOUR CODE
End Sub</PRE>

I think when I first read it, I misunderstood and thought it was referring to another method. But looking at the above, that doesn't seem intuitive to me. It seemed incorrect that the same macro is calling itself and not another macro.

So just for my understanding are both needed? Like is the on Open event required for the first time and the call within the macro is needed for subsequent runs? I believe I understand how it's being run the first time via the open event but I don't see how the subsequent runs are being triggered.

 

[VoG]

Yes you need both.

The one in the open procedure calls your macro once at a specified time.

Thye one in your macro calls your macro again at a specified time.

 

[sark666]

Ok, I'll add it to my macro and it sounds like it'll be fine. But I still am not entirely grasping how the 2nd run actually occurs.

I open the sheet. The Open event triggers the first run.

2nd day. I assume now the Open event will not run again, so I don't see what is invoking the START_procedure on the 2nd day. Even with the Application.OnTime event in my START_procedure I don't see how that is actually being triggered.

Sorry if I am being a little slow on grasping this. I'm glad it will work, but would like to understand the logical flow of it as well.

 

[prajul89]

As your START_procedure runs it will also trigger START_procedure again hence it will again run at your given time. It will be a never ending loop. Don't worry it will work.