我知道我在这里做错了什么。我试着使用sleep函数来延迟我的代码,但是我收到了"Sub或Function未定义"的错误提示。有什么建议吗?
VBA没有Sleep
函数。
你可以像这样从Kernel32.dll导入它:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
请注意,这将冻结应用程序。While
循环中调用DoEvents
,这不会冻结应用程序。我尝试的所有方法似乎都会让应用程序停止响应,包括使用Application.Wait。不过,以下这种方式似乎可行:
waitTill = Now() + TimeValue("00:15:00")
While Now() < waitTill
DoEvents
Wend
[Now()]
- HackSlashApplication.Wait T
暂停当前宏环境,而不会阻止整个过程。Application.Wait DateAdd("m", 10, Now) ' Wait for 10 Minutes
Application.Wait DateAdd("s", 10, Now) ' wait for 10 seconds
暂停应用程序10秒钟:
Application.Wait (Now + TimeValue("0:00:10"))
使用这段代码可以避免Excel冻结并且CPU使用率低:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Sub Delay(s As Single)
Dim TimeOut As Single
TimeOut = Timer + s
Do While Timer < TimeOut
DoEvents
Sleep 1 'With this line the CPU usage is 00 instead of 50 with an absolute error of +1ms and the latency of 1ms.
Loop
End Sub
#If VBA7 Then
Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#Else
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
#End If
Sub ExampleWithDelay()
Msgbox "This is a message before the delay."
Sleep 1500 ' delay of 1,000 milliseconds or 1.5 seconds
Msgbox "This is a message -AFTER- the delay."
End Sub
正如@SLaks所指出的那样,这会冻结应用程序(防止用户输入),但您也可以在While
循环中调用DoEvents
。请参见下面的示例。它运行10秒并允许用户交互。
每1/10秒,它会更新Excel状态栏,显示:
The address of the active cell
A countdown
Sub ExampleWithDelayInLoop() ' for MS Excel
Dim thisMessage As String
Dim countdownText As String
Dim i As Long
Const TOTAL_SECONDS As Byte = 10
For i = 1 To TOTAL_SECONDS * 10
countdownText = Excel.WorksheetFunction.RoundUp(TOTAL_SECONDS - (i / 10), 0)
thisMessage = "You selected " & Excel.ActiveCell.Address & Space$(4) & countdownText & " seconds remaining"
' Show the address of the active cell and a countdown in the Excel status\
' bar.
If Not Excel.Application.StatusBar = thisMessage Then
Excel.Application.StatusBar = thisMessage
End If
' Delay 1/10th of a second.
' Input is allowed in 1/10th second intervals.
Sleep 100
DoEvents
Next i
' Reset the status bar.
Excel.Application.StatusBar = False
End Sub
Await Task.Delay()
。在VBA中,我不知道是否有任何适当的方法来实现它。 - SLaksDoEvents
会导致过多的CPU使用率。请查看此解决方案,它解决了所有这些问题。 - GWD