How to pause for specific amount of time? (Excel/V

问题:

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?

Sub Macro1()
\'
\' Macro1 Macro
\'
Do
    Calculate
    \'Here I want to wait for one second

Loop
End Sub

回答1:

Use the Wait method:

Application.Wait Now + #0:00:01#

or (for Excel 2010 and later):

Application.Wait Now + #12:00:01 AM#

回答2:

Add this to your module

Public Declare Sub Sleep Lib \"kernel32\" (ByVal dwMilliseconds As Long)

Or, for 64-bit systems use:

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

回答3:

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.

回答4:

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

sub whatever()
Dim time1, time2

time1 = Now
time2 = Now + TimeValue(\"0:00:01\")
    Do Until time1 >= time2
        DoEvents
        time1 = Now()
    Loop

End sub

回答5:

The declaration for Sleep in kernel32.dll won\'t work in 64-bit Excel. This would be a little more general:

#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

回答6:

Just a cleaned up version of clemo\'s code - works in Access, which doesn\'t have the Application.Wait function.

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

回答7:

Application.Wait Second(Now) + 1

回答8:

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

回答9:

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.

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

回答10:

i had this made to answer the problem:

Sub goTIMER(NumOfSeconds As Long) \'in (seconds) as:  call gotimer (1)  \'seconds
  Application.Wait now + NumOfSeconds / 86400#
  \'Application.Wait (Now + TimeValue(\"0:00:05\"))  \'other
  Application.EnableEvents = True       \'EVENTS
End Sub

回答11:

I usually use the Timer function to pause the application. Insert this code to yours

T0 = Timer
Do
    Delay = Timer - T0
Loop Until Delay >= 1 \'Change this value to pause time for a certain amount of seconds

回答12:

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.

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:

\'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 window: 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

回答13:

Try this :

Threading.thread.sleep(1000)