如何暂停特定的时间？(Excel/VBA)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1544526/
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 pause for specific amount of time? (Excel/VBA)
提问by Keng
I have an Excel worksheet that has the following macro. I'd like to loop it every second but danged if I can find the function to do that. Isn't it possible?
我有一个 Excel 工作表，其中包含以下宏。我想每秒循环一次，但如果我能找到执行此操作的函数，则很危险。不可能吗？
Sub Macro1()
'
' Macro1 Macro
'
Do
Calculate
'Here I want to wait for one second
Loop
End Sub
回答by Ben S
Use the Wait method:
使用等待方法：
Application.Wait Now + #0:00:01#
or (for Excel 2010 and later):
或（对于 Excel 2010 及更高版本）：
Application.Wait Now + #12:00:01 AM#
回答by Buggabill
Add this to your module
将此添加到您的模块中
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Or, for 64-bit systems use:
或者，对于 64 位系统，请使用：
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)
Call it in your macro like so:
像这样在你的宏中调用它：
Sub Macro1()
'
' Macro1 Macro
'
Do
Calculate
Sleep (1000) ' delay 1 second
Loop
End Sub
回答by Achaibou Karim
instead of using:
而不是使用：
Application.Wait(Now + #0:00:01#)
i prefer:
我更喜欢：
Application.Wait(Now + TimeValue("00:00:01"))
because it is a lot easier to read afterwards.
因为以后读起来容易多了。
回答by clemo
this works flawlessly for me. insert any code before or after the "do until" loop. In your case, put the 5 lines (time1= & time2= & "do until" loop) at the end inside your do loop
这对我来说完美无缺。在“do until”循环之前或之后插入任何代码。在您的情况下，将 5 行（time1= & time2= & "do until" 循环）放在 do 循环的末尾
sub whatever()
Dim time1, time2
time1 = Now
time2 = Now + TimeValue("0:00:01")
Do Until time1 >= time2
DoEvents
time1 = Now()
Loop
End sub
回答by dbuskirk
The declaration for Sleep in kernel32.dll won't work in 64-bit Excel. This would be a little more general:
kernel32.dll 中的 Sleep 声明在 64 位 Excel 中不起作用。这会更一般一点：
#If VBA7 Then
Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
回答by Brian Burns
Just a cleaned up version of clemo's code - works in Access, which doesn't have the Application.Wait function.
只是 clemo 代码的清理版本 - 在 Access 中工作，它没有 Application.Wait 功能。
Public Sub Pause(sngSecs As Single)
Dim sngEnd As Single
sngEnd = Timer + sngSecs
While Timer < sngEnd
DoEvents
Wend
End Sub
Public Sub TestPause()
Pause 1
MsgBox "done"
End Sub
回答by cyberponk
Most of the presented solutions use Application.Wait, which does not take in account the time (miliseconds) already elapsed since the currend second count started, so they have an intrinsic imprecision of up to 1 second.
大多数提出的解决方案使用 Application.Wait，它不考虑自当前秒计数开始以来已经过去的时间（毫秒），因此它们具有高达 1 秒的内在不精确性。
The Timer approach is the best solution, but you have to take in account the reset at midnight, so here is a very precise Sleep method using Timer:
Timer 方法是最好的解决方案，但你必须考虑到午夜的重置，所以这里有一个使用 Timer 的非常精确的 Sleep 方法：
'You can use integer (1 for 1 second) or single (1.5 for 1 and a half second)
Public Sub Sleep(vSeconds As Variant)
Dim t0 As Single, t1 As Single
t0 = Timer
Do
t1 = Timer
If t1 < t0 Then t1 = t1 + 86400 'Timer overflows at midnight
DoEvents 'optional, to avoid excel freeze while sleeping
Loop Until t1 - t0 >= vSeconds
End Sub
USE THIS TO TEST ANY SLEEP FUNCTION:(open debug Immediate window: CTRL+G)
使用它来测试任何睡眠功能：（打开调试即时窗口：CTRL+G）
Sub testSleep()
t0 = Timer
Debug.Print "Time before sleep:"; t0 'Timer format is in seconds since midnight
Sleep (1.5)
Debug.Print "Time after sleep:"; Timer
Debug.Print "Slept for:"; Timer - t0; "seconds"
End Sub
回答by g t
Application.Wait Second(Now) + 1
Application.Wait Second(Now) + 1
回答by ITI
Function Delay(ByVal T As Integer)
'Function can be used to introduce a delay of up to 99 seconds
'Call Function ex: Delay 2 {introduces a 2 second delay before execution of code resumes}
strT = Mid((100 + T), 2, 2)
strSecsDelay = "00:00:" & strT
Application.Wait (Now + TimeValue(strSecsDelay))
End Function
回答by Reverus
Here is an alternative to sleep:
这是睡眠的替代方案：
Sub TDelay(delay As Long)
Dim n As Long
For n = 1 To delay
DoEvents
Next n
End Sub
In the following code I make a "glow" effect blink on a spin button to direct users to it if they are "having trouble", using "sleep 1000" in the loop resulted in no visible blinking, but the loop is working great.
在下面的代码中，我使旋转按钮上的“发光”效果闪烁，以在用户“遇到问题”时将其引导到它，在循环中使用“睡眠 1000”导致没有可见的闪烁，但循环运行良好。
Sub SpinFocus()
Dim i As Long
For i = 1 To 3 '3 blinks
Worksheets(2).Shapes("SpinGlow").ZOrder (msoBringToFront)
TDelay (10000) 'this makes the glow stay lit longer than not, looks nice.
Worksheets(2).Shapes("SpinGlow").ZOrder (msoSendBackward)
TDelay (100)
Next i
End Sub
