Cfrgtkky

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

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.

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

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

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

Application.Wait Second(Now) + 1

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

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

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

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

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

Try this :

Threading.thread.sleep(1000)