问题:

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?

回答1:

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.

回答2:

You can also pause the current macro context with Application.Wait T which won't block the whole process.

回答3:

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

回答4:

Application.Wait DateAdd("m", 10, Now) ' Wait for 10 Minutes
 Application.Wait DateAdd("s", 10, Now) ' wait for 10 seconds

回答5:

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