How to pause for specific amount of time Excel VBA

Question

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

User · Answer

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

User · Answer

Use the Wait method   Application Wait Now    0 00 01    or  for Excel 2010 and later    Application Wait Now    12 00 01 AM

User · Answer

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  gt   target End Sub   You just need to call Waste10Sec where you need the delay

User · Answer

Application Wait  Second Now    1

User · Answer

I usually use the Timer function to pause the application  Insert this code to yours  T0   Timer Do     Delay   Timer - T0 Loop Until Delay  gt   1  Change this value to pause time for a certain amount of seconds

User · Answer

Try this    Threading thread sleep 1000

User · Answer

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

User · Answer

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

User · Answer

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    amp  strT     Application Wait  Now   TimeValue strSecsDelay   End Function

User · Answer

this works flawlessly for me  insert any code before or after the  do until  loop  In your case  put the 5 lines  time1   amp  time2   amp   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  gt   time2         DoEvents         time1   Now       Loop  End sub

User · Answer

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  lt  t0 Then t1   t1   86400  Timer overflows at midnight         DoEvents     optional  to avoid excel freeze while sleeping     Loop Until t1 - t0  gt   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

User · Answer

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  lt  sngEnd         DoEvents     Wend End Sub  Public Sub TestPause       Pause 1     MsgBox  done  End Sub

User · Answer

You can use  Application wait now   timevalue  00 00 01   or Application wait now   timeserial 0 0 1

User · Answer

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

User · Answer

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

[excel] How to pause for specific amount of time? (Excel/VBA)

Examples related to excel

Examples related to vba