MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Tom - Help - Workbook keeps reopening


Posted by Sam on January 09, 2002 2:31 PM

Tom you helped me out with the following macro to recalculate a worksheet every minute. Worked well, too well as the workbook keeps reopening after 1minute after being closed/saved so long as I have Excel running. Any ideas as it is starting to frustrate me. Thanks in advance.

Private Sub Worksheet_Activate()
Call Recalculate
End Sub

and then paste this code in a new module in the VBE (that is, press Alt+F11, click Insert > Module):

Public Sub Recalculate()
Calculate
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"), _
procedure:="Recalculate"
End Sub


Posted by Tom Urtis on January 09, 2002 4:19 PM

Hi Sam,

Thanks a lot for writing. You know what, I did not experience that problem with the mock workbook when I sent you the code originally, but after seeing your post here I tried it again and you are absolutely correct.

I tested this situation under numerous conditions but could not figure an elegant solution. The unelegant workaround, as you probably discovered yourself, is to close Excel and reopen it, which is inconvenient when other applications are open.

I just don't know the answer right now (probably something obvious we're missing), but if anyone reading this can shed light, please chime in.

Thanks again for the follow up.

Tom Urtis

Posted by Sam S on January 09, 2002 5:34 PM

Thanks Tom - really appreiate your help
BTW we had a severe storm the day after and the staff here really appreciated your procedure that I incorporated.

Posted by Tom Urtis on January 09, 2002 5:47 PM

Glad I could help.

I keep thinking this solution might be found in the VBE somehow. I'll keep thinking about it, and there are some highly talented programmers who visit this site and may well know the answer.

T.U.

Posted by Ivan F Moala on January 09, 2002 8:24 PM

Tom Sam - Help - Workbook keeps reopening

Tom / Sam with application Ontime you must
schedule it to shut down via Schedule:=False
Also be careful how you call it eg


Public Sub Recalculate()
Calculate
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"), _
procedure:="Recalculate"
End Sub

Public Sub Recalculate()
Application.OnTime earliesttime:=Now + TimeValue("00:01:00"), _
procedure:="Recalculate",Schedule:=False
End Sub

WILL NOT CANCEL THE JOB. This is because the procedure AND the Time variable are linked.
So you have to do something like this;

Public Thistime As Double

Public Sub Recalculate()
Calculate
Thistime = Now + TimeValue("00:01:00")
Application.OnTime Thistime, Procedure:="Recalculate"
End Sub

Public Sub stopCalc()
Application.OnTime Thistime, Procedure:="Recalculate", Schedule:=False
End Sub


Also call it @ the Workbook before close event
to STOP the calc routine from running. This is what
you were experiencing when you ran the routine.
Because it wasn't stoped properly it would load
up every minute.


HTH


Ivan

Posted by Tom Urtis on January 10, 2002 3:08 AM

Thanks Ivan, that's it, plus theory ramblings.......

Thank you Ivan, that's it. I totally forgot about that fourth parameter (and its syntax) of setting it to False, for cancelling the next scheduled run stored in the module.

Your way is definitely the way to go, but thinking about this now, one other awkward way (not recommended, just theory ramblings here) is if the name of the procedure is changed, an error would take place at the next scheduled procedure call because the macro couldn't be found. Also, a comment might do it at the ThisTime instruction, which should end it at the next procedure call. Neither method is practical, I'm just testing my understanding and could be wrong.

Thanks again for your help.

Tom Urtis

: Glad I could help. : I keep thinking this solution might be found in the VBE somehow. I'll keep thinking about it, and there are some highly talented programmers who visit this site and may well know the answer. : T.U. :

Posted by Sam S on January 10, 2002 1:18 PM

Re: Thanks Ivan, that's it, plus theory ramblings.......

Thank you Ivan and Tom - my head is spinning, how do you fellows work out all this stuff. Thank you Ivan, that's it. I totally forgot about that fourth parameter (and its syntax) of setting it to False, for cancelling the next scheduled run stored in the module. Your way is definitely the way to go, but thinking about this now, one other awkward way (not recommended, just theory ramblings here) is if the name of the procedure is changed, an error would take place at the next scheduled procedure call because the macro couldn't be found. Also, a comment might do it at the ThisTime instruction, which should end it at the next procedure call. Neither method is practical, I'm just testing my understanding and could be wrong. Thanks again for your help.

: Tom / Sam with application Ontime you must : schedule it to shut down via Schedule:=False : Also be careful how you call it eg : : Public Sub Recalculate() : Public Sub Recalculate() ,Schedule:=False : WILL NOT CANCEL THE JOB. This is because the procedure AND the Time variable are linked. : So you have to do something like this; : Public Thistime As Double : Public Sub Recalculate() : Thistime = Now + TimeValue("00:01:00") : Application.OnTime Thistime, Procedure:="Recalculate" : Public Sub stopCalc() : Application.OnTime Thistime, Procedure:="Recalculate", Schedule:=False : : Also call it @ the Workbook before close event : to STOP the calc routine from running. This is what : you were experiencing when you ran the routine. : Because it wasn't stoped properly it would load : up every minute. : : HTH : : Ivan :

Posted by Sam S on January 10, 2002 2:10 PM

Sub stopCalc falls over

Ivan question to clear up something for a VBA novice(self taught and trying to get my mind around it).

Why does "Double" do for a variable?

Also Public Sub stopCalc()falls over with "Method 'OnTime' of Object'_Application' failed - it doesn't like Schedule:=False

Your last comment mentioned to call it @ the Workbook before close event, I'm not exactly sure what is required here - could you elucidate.

Sorry to keep bothering you with what are probably basics. Tom / Sam with application Ontime you must