Can't stop my Application.OnTime macros

[roheba]

I subscribe to a service that sends real-time updating stock prices directly to my spreadsheets. (Hoadley Financial Add-Ins for Excel.)

Using various posts on this site, and much trial and error, I cobbled together a little VBA code that monitors these stock prices and notifies me when my open position's profit has reached a new high and when it falls X percent below that high. I am using scheduled Application.OnTime updates to revise my potential gain when new highs are reached and to trigger an announcement when the percent drop is reached. My code is working well with one significant exception -- My macro to discontinue the Application.OnTime updates is not stopping the process. I think I have not yet formatted the command properly, but I really do not know what I am doing wrong.

I have listed my Application.OnTime macros below. (I suspect there's a better way to post my code as an attachment or insert to MrExcel, but I couldn't find any instruction for doing this. Apologies.) There are other macros referenced in the below code but I did not include them because I believe they are irrelevant to my current issue.

Once the updating process is initiated it continues indefinitely, even after I invoke the StopTimer macro.

I would really appreciate it if you would critique my code and suggest how I can revise the StopTimer macro to turn off the Application.OnTime updates. Thanks.

Here are the macros:

SubSetTime()
'set the interval for running this code from "Now"
SchedRecalc= Now + TimeValue("00:05:00")
Application.OnTimeSchedRecalc, "Recalc"
End Sub

SubRecalc()
'code to achieve task goes here
Application.Run"Macro1"
Application.Run"Macro2"
Range("F21").Value= Now
CallSetTime
End Sub

SubStopTimer()
On ErrorResume Next
Application.OnTimeSchedRecalc, "Recalc", ,schedule:=False
WithApplication.Speech
.Speak("Automatic updating is stopped.")
End With
End Sub

 

[AlphaFrog]

Declare the SchedRecalc variable at the very top of the code module so the StopTimer procedure can access it.

Public SchedRecalc As Date


Sub SetTime()
'set the interval for running this code from "Now"
    SchedRecalc = Now + TimeValue("00:05:00")
    Application.OnTime SchedRecalc, "Recalc"
End Sub


Sub Recalc()
'code to achieve task goes here
    Application.Run "Macro1"
    Application.Run "Macro2"
    Range("F21").Value = Now
    Call SetTime
End Sub


Sub StopTimer()
    On Error Resume Next
    Application.OnTime SchedRecalc, "Recalc", Schedule:=False
    Application.Speech.Speak ("Automatic updating is stopped.")
End Sub

Also, add this to the ThisWorkbook code module.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTimer
End Sub

 

[ZVI]

Add this code line in the top of your code module before any Subs:
Dim SchedRecalc As Date

 

[Logit]

Sub StopTimer()
On Error Resume Next
Application.OnTime Now + TimeValue("00:00:01"), "Recalc", , False
With Application.Speech
.Speak ("Automatic updating is stopped.")
End With
End Sub

 

[roheba]

Thanks to each of the three repliers to my post. After trying each suggestion, I determined that the response provided by AlphaFrog was the ultimate solution to my problem. I really appreciate the effort... and I now thoroughly enjoy being able to stop my process without having to close the entire file.