Unload Excel Sub From Memory

[ak10702]

I am using the subs:

Sub MySub()
Call OtherSub
Application.OnTime Now + TimeValue("00:15:00&quot, "MySub"

End Sub


Sub OncePerHour()
Application.OnTime TimeValue("08:59:00&quot, "OtherSub"
Application.OnTime TimeValue("09:59:00&quot, "OtherSub"
Application.OnTime TimeValue("10:59:00&quot, "OtherSub"
Application.OnTime TimeValue("11:59:00&quot, "OtherSub"
Application.OnTime TimeValue("12:59:00&quot, "OtherSub"
Application.OnTime TimeValue("13:59:00&quot, "OtherSub"
Application.OnTime TimeValue("14:59:00&quot, "OtherSub"
Application.OnTime TimeValue("15:59:00&quot, "OtherSub"
Application.OnTime TimeValue("16:57:00&quot, "OtherSub"
Application.OnTime TimeValue("16:59:00&quot, "OtherSub"
Application.OnTime TimeValue("17:59:00&quot, "OtherSub"
Application.OnTime TimeValue("18:59:00&quot, "OtherSub"
Application.OnTime TimeValue("19:59:00&quot, "OtherSub"
Application.OnTime TimeValue("20:59:00&quot, "OtherSub"
Application.OnTime TimeValue("21:59:00&quot, "OtherSub"
Application.OnTime TimeValue("22:57:00&quot, "OtherSub"
Application.OnTime TimeValue("22:59:00&quot, "OtherSub"
Application.OnTime TimeValue("23:59:00&quot, "OtherSub"

End Sub

this will call OtherSub every 15 minutes and at the specified times.

The problem I am having is that if I close the workbook and dont close excel then in 15 minutes excel calls the sub and the worbook opens up again automatically.

Also Sometimes instead of opening the workbook I get an error the the memory can't be read at 0x0000002 and excel terminates.

I think all that needs to be done is unload the Application.OnTime from memory, but I dont know how.

Help

 

[IvanMoala]

Look up Ontime in your VBA help.

Basically what is happening is you are Calling a time event
BUT NOT closing it off when done....similar to establishing a TimerID handle to the API Timer function and then not destroying it when done.

eg called
Application.OnTime TimeValue("08:59:00&quot, "OtherSub"

Closed should be
Application.OnTime TimeValue("08:59:00&quot, "OtherSub", schedule:=False

This should be done for All ontime functions you call.

 

[ak10702]

I've tried:

Application.OnTime Now + TimeValue("00:15:00&quot, "OtherSub", schedule:=False

Application.OnTime Now + TimeValue("00:15:00&quot, "OtherSub", , schedule:=False

Application.OnTime Now + TimeValue("00:15:00&quot, "OtherSub","", schedule:=False

Application.OnTime (Now + TimeValue("00:15:00&quot, "OtherSub","", schedule:=False)

Application.OnTime (Now + TimeValue("00:15:00&quot, "OtherSub", schedule:=False)

and all I get is:

Run Time Error 1004
method 'OnTime' of Object '_Application' Failed

or for the last two a compile error expected =

What am I doing wrong

 

[IvanMoala]

You are using the Volitile Now() Function to establish a handle to your timer event....your'll need to assign this to a variable so the Handle can be established

eg

IdTimer1 = Now + TimeValue("00:15:00&quot
Application.OnTime IdTimer1, "OtherSub"

then to close it off

Application.OnTime IdTimer1, "OtherSub",False

 

[ak10702]

IvanMoala

Thanks for the advice I tried what you said and got the false option to work without a compile error but The application still comes back every 15 min.

help

 

[IvanMoala]

Each time you call the Ontime method (With your Timer ID)
Your called routine should then Dismiss it as given above.

have a look here for example

http://www.cpearson.com/excel/ontime.htm

 

[ak10702]

Thanks It worked