how to use Application.OnTime to delay a loop

Question

SO I'm writing a code which makes references to bloomberg cell formulas... as a result the loop often skips faster than data can load. I think this can be solved relatively easily if I can somehow delay the iteration of the loop ... which would give cells time to populate. This is what I have written so far, and I'm not sure how to write the last line really.

x = 1


If x < TixCollection.count Then

    ' runs report if ADR Close is active
    If Sheets("Input").Range("L2").value = "x" Then
        Call build_singleEquity(x)
            'Set pat = New pattern

            Application.OnTime Now + TimeValue("00:00:10"), "pattern_recogADR"

            If Sheets("Input").Range("L5").value = "x" Then
                Dim sht_name As String
                sht_name = TixCollection(x).ADR & "_ADRclose"
                Call Sheet_SaveAs(path_ADRclose, sht_name, "SingleEquityHistoryHedge")
            End If
    End If


    'runs report if ORD Close is active
    If Sheets("Input").Range("L9").value = "x" Then
        Call build_ordCloseTrade(x)



            If Sheets("Input").Range("L13").value = "x" Then

                Dim sht_name1 As String
                sht_name1 = TixCollection(x).ADR & "_ORDclose"
                Call Sheet_SaveAs(path_ORDclose, sht_name1, "OrdCloseTrade")
            End If
    End If


Application.OnTime Now + TimeValue("00:00:15"), x = x + 1 'want to write something like this but syntax is wrong 

Answer 1

Application.OnTime essentially schedules an event to run at/after a particular time. It allows you to schedule an event to run even after your code has executed and cleared the stack. This is why it's an Application level method.

The OnTime method doesn't pause or delay anything, it's simply a scheduler. So the rest of your code will continue to execute, and to whatever extent that code relies on the results of the task that is waiting for the OnTime, you'll run in to errors.

In theory, I think you could probably make this approach work in a roundabout way for your purposes, but I think you'd probably be better served to use the WinAPI Sleep function. This also gives you greater granularity (you can specify to the millisecond).

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

You can invoke this from any subroutine or function, by calling:

Sleep 5000 'pause for 5000ms, (5 seconds)

UPDATE

I notice your code doesn't actually contain a Loop structure. I think I understand what you're trying to do. Here's a stab at it:

Do While x < TixCollection.count Then  `or consider: Do While x <= TixCollection.Count

    ' runs report if ADR Close is active
    If Sheets("Input").Range("L2").value = "x" Then
        Call build_singleEquity(x)
            'Set pat = New pattern

            Sleep 10000
            Call pattern_recogADR

            If Sheets("Input").Range("L5").value = "x" Then
                Dim sht_name As String
                sht_name = TixCollection(x).ADR & "_ADRclose"
                Call Sheet_SaveAs(path_ADRclose, sht_name, "SingleEquityHistoryHedge")
            End If
    End If


    'runs report if ORD Close is active
    If Sheets("Input").Range("L9").value = "x" Then
        Call build_ordCloseTrade(x)
            If Sheets("Input").Range("L13").value = "x" Then

                Dim sht_name1 As String
                sht_name1 = TixCollection(x).ADR & "_ORDclose"
                Call Sheet_SaveAs(path_ORDclose, sht_name1, "OrdCloseTrade")
            End If
    End If

x = x+1
Sleep 15000  'Wait for 15 seconds  
Loop

It is hard to tell if both of the Sleep (your previous OnTime calls) are really needed, since I'm not sure which one (or if both) was introducing the error condition.

You will need to make sure that you put the code for the Sleep function in the vba project.

Update

Assuming the sleep function, or Application.Wait method does not work for you, one other thing you could try is a simple Do/While Loop. Although I am not able to replicate your condition, this seems like possibly the most reliable.

Dim newTime As Date
newTime = Now + TimeValue("00:00:10")
Do While Not Now >= newTime
    DoEvents
Loop

A final option would be to disable and manually force calculation, like below. My understanding is that the application is busy and will not execute code while a calculation event is occurring. However, with this in mind I'm not sure if any of these approaches will work for you, because although you indicate it's waiting on an Excel worksheet calculation, I don't think that is possible, the worksheet event takes precedence over running code, so I'm thinking that something is still happening on the client side which you might not be able to trap reliably unless they provide some sort of method through the API (something like .Busy which returns a boolean, etc.).

Dim appCalc as Long: appCalc = Application.Calculation
Application.Calculation = appCalc  '# disable automatic calculation
Call build_singleEquity(x)
Application.Calculate 'Force calculation
Application.Calculation = xlCalculationAutomatic  '# return to normal/previous property
Call pattern_recogAD