开发者

VBA Sleep Doesn't Work [closed]

Closed. This question needs debugging details. It is not currently accepting answers.

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:

Original close reason(s) were not resolved

Improve this question

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:

  1. The address of the active cell

  2. 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
    
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜