Application.OnTime Huge Issues

[sparx]

Help Help Help......

I am really struggling trying to get this routine to stop when I close the workbook, the workbook just re-opens by itself and continues to run.

I am running the Application.OnTime routine every 15 seconds, and it runs a macro that checks a folder for a file.

I have added the stop routine to the close.workbook and I still cant get the timer to stop, where am I going wrong?

In a module:

Please Login or Register  to view this content.

In the workbook close section

Please Login or Register  to view this content.

On the end of the macro called RunMacro1, it calls for the starttimer to start again, does this cause 2 timers to run at the same time, so am I right in thinking the stoptimer may actually stop one of the timers but the 2nd is the one causing Excel to keep re-opening by itself.

[shg]

Whats the code where RunMacro1 reschedules?

[joeu2004]

On the end of the macro called RunMacro1, it calls for the starttimer [sic] to start again, does this cause 2 timers to run at the same time, so am I right in thinking the stoptimer may actually stop one of the timers but the 2nd is the one causing Excel to keep re-opening by itself.

If you mean SetTimer, DownTime gets over-written the 2nd time. So actually you are stopping the 2nd timer, but not the 1st one.

You need to keep track of and stop each OnTime event that is scheduled simultaneously.

[EDIT] Or perhaps scheduling two OnTime events simultaneously is unintentional. In that case, in RunMacro1, use On Error Resume Next and OnTime...False to stop the first event, if any, before scheduling the second event.

FYI, there is also a race condition. If the timeout for an OnTime event occurs before StopTimer can abort it, the OnTime event will occur after the close again.

I was going to post code to demonstrate the race condition as well as the fix -- until I read this last line, which I overlooked in my first reading.

If the OnTime event is triggered only every 15 sec or more, it is unlikely that the race condition will occur.

So it might not be worth the effort for you to understand and avoid it.

Let me know if you are still interested.

[sanram]

These codes are working well on my system:

Please Login or Register  to view this content.

[sparx]

Hi all, here is the macro the timer is running - I am basically dumping a text file into a folder and then making the macro look for the file, its acting like a messaging system within Excel and works pretty good except Excel keeps opening by itself.

Please Login or Register  to view this content.

When the RunMacro1 runs, its checking for a file, if the file exists, a form opens up that displays the text like a messenger service then it will start the timer to check after another 15 seconds.

What I didn't mention in the first message which I have just noticed is the following which I have now disabled.

Please Login or Register  to view this content.

When the workbook opens it looks for the text file straight away, then if it sees one it displays the form which in itself runs another version of the timer, I think whats happening is I am running the timer multiple times and am loosing track of which timer needs to be stopped.
If only there was a global option to stop all active timers.

[sparx]

Hi all, here is the macro the timer is running - I am basically dumping a text file into a folder and then making the macro look for the file, its acting like a messaging system within Excel and works pretty good except Excel keeps opening by itself.

Please Login or Register  to view this content.

When the RunMacro1 runs, its checking for a file, if the file exists, a form opens up that displays the text like a messenger service then it will start the timer to check after another 15 seconds.

What I didn't mention in the first message which I have just noticed is the following which I have now disabled.

Please Login or Register  to view this content.

When the workbook opens it looks for the text file straight away, then if it sees one it displays the form which in itself runs another version of the timer, I think what's happening is I am running multiple timers at different times and am loosing track of which timer needs to be stopped.
If only there was a global option to stop all active timers.

[sparx]

I think we have solved the issues with your help and my mis-understanding of how the timers worked, At file open the timer was told to start, if a file existed it would show the file as a message then the timer would start again, I believe I was running multiple timers so have scrapped all the code relating to starting timers - now I have the following:

File open - start timer
Find file - stop timer - open file & display text - start timer - if no file after 15 seconds, start timer again & loop until find file.
File close - Stop timer

I have gone with this and have closed the file in Excel 2002/2003 and Excel 2010 and it doesn't re-open by itself.
The file remains closed so the stop-timer must be working, yippee.