vba 如何使用 Application.OnTime 延迟循环
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18949660/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to use Application.OnTime to delay a loop
提问by googlekid
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.
所以我正在编写一个代码来引用bloomberg单元格公式......因此循环跳过的速度通常比数据加载的速度快。我认为这可以相对容易地解决，如果我能以某种方式延迟循环的迭代......这将使细胞有时间填充。这就是我到目前为止所写的，我真的不知道如何写最后一行。
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
回答by David Zemens
Application.OnTimeessentially schedules an event to run at/after a particular time. It allows you to schedule an event to run even afteryour code has executed and cleared the stack. This is why it's an Applicationlevel method.
Application.OnTime本质上是安排一个事件在特定时间/之后运行。它可以让你来安排，即使运行事件后，你的代码执行和清算堆栈。这就是为什么它是一种Application水平方法。
The OnTimemethod doesn't pauseor 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.
该OnTime方法不会暂停或延迟任何事情，它只是一个调度程序。因此，您的其余代码将继续执行，并且无论该代码在多大程度上依赖于等待 的任务的结果OnTime，您都会遇到错误。
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 Sleepfunction. This also gives you greater granularity (you can specify to the millisecond).
从理论上讲，我认为您可能可以使这种方法以一种迂回的方式工作以达到您的目的，但我认为您最好使用 WinAPISleep函数。这也为您提供了更大的粒度（您可以指定为毫秒）。
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 Loopstructure. I think I understand what you're trying to do. Here's a stab at it:
我注意到您的代码实际上并不包含Loop结构。我想我明白你想要做什么。这是一个尝试：
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 OnTimecalls) are really needed, since I'm not sure which one (or if both) was introducing the error condition.
很难判断是否真的需要这两个Sleep（您之前的OnTime调用），因为我不确定是哪个（或两个）引入了错误条件。
You will need to make sure that you put the code for the Sleepfunction in the vba project.
您需要确保将Sleep函数的代码放在vba 项目中。
Update
更新
Assuming the sleep function, or Application.Waitmethod does not work for you, one other thing you could try is a simple Do/WhileLoop. Although I am not able to replicate your condition, this seems like possibly the most reliable.
假设睡眠功能或Application.Wait方法对您不起作用，您可以尝试的另一件事是简单的Do/While循环。虽然我无法复制您的情况，但这似乎可能是最可靠的。
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 .Busywhich returns a boolean, etc.).
最后一个选项是禁用并手动强制计算，如下所示。我的理解是应用程序很忙，不会在计算事件发生时执行代码。但是，考虑到这一点，我不确定这些方法中的任何一种是否适合您，因为尽管您表示它正在等待 Excel 工作表计算，但我认为这是不可能的，工作表事件优先于运行代码，所以我认为客户端仍在发生某些事情，除非他们通过 API 提供某种方法（例如.Busy返回布尔值等），否则您可能无法可靠地捕获这些内容。
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
