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 15 Answers
Use the Wait method:
Application.Wait Now + #0:00:01# or (for Excel 2010 and later):
Application.Wait Now + #12:00:01 AM# 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.
2this 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 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 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 Wait and Sleep functions lock Excel and you can't do anything else until the delay finishes. On the other hand Loop delays doesn't give you an exact time to wait.
So, I've made this workaround joining a little bit of both concepts. It loops until the time is 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 the delay
0Application.Wait Second(Now) + 1
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 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 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 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 You can use Application.wait now + timevalue("00:00:01") or Application.wait now + timeserial(0,0,1)
Try this :
Threading.thread.sleep(1000) 
