Application.OnTime privilege (Not running when data is updating constantly)

Question

Initially I turn on automatic calculation.

There are 2 worksheets: A1 and B1.
A1 pulls data from Bloomberg API constantly. (I am using BDP LAST_PRICE.)
B1 takes the data from A1 to do some calculations.

Now I need to turn off automatic calculation every 10 minutes or so.
I am using Application.OnTime to schedule a macro to turn off Excel automatic calculation.
According to: https://docs.microsoft.com/en-us/office/vba/api/Excel.Application.OnTime, Application.OnTime will only run when Excel is in "Ready, Copy, Cut, or Find mode".
Given the data updates constantly and we are doing constant calculations, Excel will never be in one of those modes. Therefore, the Application.OnTime macro will never execute.

Below are the methods I tried:

I set "LatestTime" parameter to 10 seconds after "EarliestTime" but it does not seem to work.
Instead of scheduling in the future, I Call a new function whenever an iteration is complete and try to wait for some time. The rough structure is as follows:

Sub QuitAuto()
    Application.Wait (Now + TimeValue("00:00:10"))
    'Turn off auto
    Call wait_func
End Sub    
    
Sub wait_func()
    Application.OnTime ..."AnotherIter"
End Sub    
    
Sub AnotherIter()
    'Turn on auto
    Call QuitAuto
End Sub

In this case, Application.WaitTime is going to stop both the macro and the data pulling process.

I also tried a while loop & doEvents approach, Excel is not frozen but the data pulling process still does not seem to work.

Is there any way to bypass the "low privilege" of the Application.OnTime issue?
Or is there any way to let the macro wait and at the same time let the Excel formula run in the background?

Does either of your sheets trigger the Worksheet_Calculate() event? If so, you could use that event to execute your scheduled task. Create a Module scoped variable to hold the scheduled time, and check If Now() >= ScheduledTime Then to trigger your required code — chris neilsen
@chrisneilsen Yes, I think so! Because in B1 there are some formulas constantly using the changing prices in A1 to do some calculations, like summing up prices times positions etc. I will give it a try and let you know whether it works! Thank you so much for your help! — EZZZZ

chris neilsen chris neilsen · Accepted Answer · 2020-12-17T01:44:05

If one of your sheets, probably B1, periodically triggers a Worksheet_Calculate event, you could use that to trigger your scheduled code.

In sheet B1 code module, add this

Option Explicit

Public DoOnTime As Date
Private Const SchedulePeriod As Date = "00:10:00" ' 10 minutes


Private Sub Worksheet_Calculate()
    Dim DoAt As Date
    If Now() >= DoOnTime Then
        ' Do your scheduled stuff here
        ' ...

        DoOnTime = Now() + SchedulePeriod
        Debug.Print "Triggered at", Now(), "Scheduled at", DoOnTime
    End If
End Sub

For testing purposes, I used this in a standard moule to simulate a busy workbook (With ScheduledPeriod set to 10sec)

Option Explicit

Sub BeBusy()
    Dim TerminateAt As Date
    Dim Last As Date
    TerminateAt = Now() + TimeValue("00:30:00")
    Do
        If Last <> Now() Then
            Debug.Print "Busy", Now(), "Scheduled", Worksheets("B1").DoOnTime
            Last = Now()
            Worksheets("B1").[a1].Calculate
            DoEvents
        End If
    Loop Until Now() >= TerminateAt
End Sub

Immediate window output

Busy 17/12/2020 14:41:47 Scheduled 17/12/2020 14:40:46
Triggered at 17/12/2020 14:41:47 Scheduled at 17/12/2020 14:41:57
Busy 17/12/2020 14:41:48 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:49 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:50 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:51 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:52 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:53 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:54 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:55 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:56 Scheduled 17/12/2020 14:41:57
Busy 17/12/2020 14:41:57 Scheduled 17/12/2020 14:41:57
Triggered at 17/12/2020 14:41:57 Scheduled at 17/12/2020 14:42:07
Busy 17/12/2020 14:41:58 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:41:59 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:00 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:01 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:02 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:03 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:04 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:05 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:06 Scheduled 17/12/2020 14:42:07
Busy 17/12/2020 14:42:07 Scheduled 17/12/2020 14:42:07
Triggered at 17/12/2020 14:42:07 Scheduled at 17/12/2020 14:42:17
Busy 17/12/2020 14:42:08 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:09 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:10 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:11 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:12 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:13 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:14 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:15 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:16 Scheduled 17/12/2020 14:42:17
Busy 17/12/2020 14:42:17 Scheduled 17/12/2020 14:42:17
Triggered at 17/12/2020 14:42:17 Scheduled at 17/12/2020 14:42:27
Busy 17/12/2020 14:42:18 Scheduled 17/12/2020 14:42:27
Busy 17/12/2020 14:42:19 Scheduled 17/12/2020 14:42:27
Busy 17/12/2020 14:42:20 Scheduled 17/12/2020 14:42:27

Application.OnTime privilege (Not running when data is updating constantly)

1 Answers