CODEWITHSUNDEEP
excelvba
sn2006
sn2006

Reputation: 3

Excel Application.OnTime additional time delay

I am using a macro to run a web query every 30 minutes. It takes about 45-75 seconds to fetch data from website. Basically it is working fine with a small problem. I first start it at 9:00am manually. It takes about 1 min to complete. Then it works again on 9:31, 10:01, 10:31, 11:02 etc. I want it should be 9:00, 9:30, 10:00 etc. I also need it to start automatically at 9:00 am if file is already open. I am using the following code:

Sub RefreshAll()
    Call macro1
    Application.OnTime Now + TimeValue("00:30:00"), "RefreshAll"
End Sub

Sub macro1()
    'web query code here
End Sub

Please suggest something.

Upvotes: 0

Views: 1389

Answers (2)

BigBen
BigBen

Reputation: 49998

  1. For the schedule to be 9:00, 9:30, and so on, move the Application.OnTime call to the first line:

    Sub RefreshAll()
    Application.OnTime Now + TimeValue("00:30:00"), "RefreshAll"
    Call macro1
End Sub
  2. To have RefreshAll run automatically at 9:00 (if you are opening the workbook manually before): 
    Private Sub Workbook_Open()
    Application.OnTime TimeValue("09:00:00"), "RefreshAll"
End Sub

Upvotes: 0

Pᴇʜ
Pᴇʜ

Reputation: 57683

Either run Application.OnTime before you call macro1 as @BigBen suggested, or another approach would be to write the start time of the macro into a variable. So no matter how long macro1 takes to proceed it will use the start time 9:00 + 30 min.

Then check if your macro1 run successfully and if so set the OnTime. This way it won't set the OnTime if fetching the data failed.

Sub RefreshAll()
    Dim StartTime As date
    StartTime = Now 'remember the start time of the macro = 9:00

    If macro1 Then 'run macro1 and check if it returned true
        Application.OnTime StartTime + TimeValue("00:30:00"), "RefreshAll"
    End If
End Sub

Function macro1() As Boolean
    On Error Goto ERR_HANDLING

    'web query code here

ERR_HANDLING:
    If Err.Number = 0 Then
        macro1 = True 'return true if no errors
    Else
        MsgBox "Could not fetch data"
    End If
End Function

If there is the possibility that the runtime of macro1 exceeds the TimeValue that you add, you should check for that like 

    If macro1 Then 'run macro1 and check if it returned true
        If StartTime + TimeValue("00:30:00") > Now Then
            Application.OnTime StartTime + TimeValue("00:30:00"), "RefreshAll"
        Else
            MsgBox "Could not set OnTime because it was in the past."
        End If
    End If

Upvotes: 1

Related Questions