Stopping Code Execution -- Newbie

[John V]

Here is an abbreviated version of code that I have borrowed:

Public RunWhen As Double
Public Const cRunIntervalSeconds = 120 ' two minutes
Public Const cRunWhat = "GetData" ' the name of the procedure to run


Sub StartTimer()
RunWhen = Now + TimeSerial(0, 0, cRunIntervalSeconds)
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=True
End Sub

Sub GetData()

<code to pull stock quotes via Web Query>
StartTimer

End Sub
Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=RunWhen, Procedure:=cRunWhat,
Schedule:=False
End Sub

Questions:
1. I have occasion to put this "on pause" while I update other aspects of
the workbook. I envision one or two buttons that halt execution, then restart
it. How might I do that?
2. What additional code would be needed to stop the queries between the
hours of, say, 4pm and 8am?

Many thanks.

 

[Chip Pearson]

John,

That looks like some of my code. You can use buttons to interupt the OnTime
calls by calling your StopTimer function from the "Halt" button and then
call StartTimer from your "Resume" button. E.g.,

Private Sub btnHalt_Click()
StopTimer
End Sub

Private Sub btnResume_Click()
StartTimer
End Sub

You can pause the OnTime actions with code in the GetData function similar
to the following:

Sub GetData()
'''''''
' your code here
'''''''
If Hour(Now) >= 16 Then ' 16 = 4PM
StopTimer()
Application.OnTime Int(Now) + 1 + TimeSerial(8, 0, 0), "GetData", , True
' 8AM Next Day
End If
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2008
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)