How to pause a certain amount of time? (Excel / vba)

Question

I have an Excel worksheet that has the following macro. I would like to loop it every second, but I can be used if I can find a function for this. Is it impossible?

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

Answer 1

Use the Wait Method :

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

or (for Excel 2010 and later):

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

Answer 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 your macro as follows:

 Sub Macro1() ' ' Macro1 Macro ' Do Calculate Sleep (1000) ' delay 1 second Loop End Sub 

Answer 3

instead of using:

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

I prefer:

 Application.Wait(Now + TimeValue("00:00:01")) 

because after that it’s much easier to read.

Answer 4

It works flawlessly for me. insert any code before or after the do-before loop. In your case, put 5 lines (time1 = and time2 = and "do until") at the end inside the 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 

Answer 5

The ad for sleeping in kernel32.dll will not work in 64-bit Excel. This will 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 

Answer 6

Just cleared version of clemo code - works in Access, which does not have 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 

Answer 7

Application.Wait Second(Now) + 1

Answer 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 

Answer 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 the glow effect on the scroll button blink to direct users to it if they have "problems", using "sleep 1000" in the loop did not cause a visible blink, but the loop works fine.

 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 

Answer 10

Most of the solutions presented use Application.Wait, which does not take into account the time (milliseconds) that has already elapsed since the start of the second count, so they are inaccurate up to 1 second .

A timer approach is the best solution , but you should consider resetting at midnight, so here is a very accurate 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 CHECK ANY SLEEP FUNCTION: (open debug Immediate 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 

Answer 11

I did this 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 

Answer 12

I usually use the timer function to pause the application. Paste this code into your

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

Answer 13

The wait and sleep functions block Excel, and you cannot do anything else until the delay ends. Loop delays, on the other hand, do not give you exact latency.

So, I made this workaround, combining a bit of both concepts. It loops until time becomes the time you want.

 Private Sub Waste10Sec() target = (Now + TimeValue("0:00:10")) Do DoEvents 'keeps excel running other stuff Loop Until Now >= target End Sub 

You just need to call Waste10Sec where you need a delay

Answer 14

Try the following:

 Threading.thread.sleep(1000)