VBA Sleep Doesn't Work [closed]
Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question.
开发者_JS百科Closed last month.
The community reviewed whether to reopen this question last month and left it closed:
Improve this questionOriginal close reason(s) were not resolved
I know I'm doing something wrong here. I'm trying to use the sleep function to delay my code, but I get "Sub or Function not defined" error. Any tips?
VBA does not have a Sleep
function.
You can import it from Kernel32.dll like this:
Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
Note that this will freeze the application.
You can also call DoEvents
in a While
loop, which won't freeze the application.
Everything I've tried seems to hang the application, including Application.Wait. This seems to work though:
waitTill = Now() + TimeValue("00:15:00")
While Now() < waitTill
DoEvents
Wend
You can also pause the current macro context with Application.Wait T
which won't block the whole process.
Application.Wait DateAdd("m", 10, Now) ' Wait for 10 Minutes
Application.Wait DateAdd("s", 10, Now) ' wait for 10 seconds
With this code Excel not freeze and the CPU usage is low:
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
Pausing an application for 10 seconds:
Application.Wait (Now + TimeValue("0:00:10"))
Here's what you need for cross compatability with 32-bit and 64-bit Windows machines. The delay is in milliseconds, so use 1000 for a 1 second delay.
First, put this above your other Subs/Functions in a module. On a 64-bit machine the line after "#Else" will be highlighted as if there is an error, but this isn't an issue. The code will compile and run.
#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
Now you can create a delay like this example with a 1.5 second delay:
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
As noted by @SLaks, this freezes the application (preventing user input) but you can also call DoEvents
in a While
loop. See the example below. It runs for 10 seconds and allows user interaction.
Every 1/10th of a second it updates the Excel status bar with:
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
精彩评论