Pause vba for 20 seconds

[ste33uka]

I have the following vba code that runs every 5 minutes
Would it be possible to pause it for 20seconds after the line
.Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)
And that excel would not be frozen?
Thanks

Sub test()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
   

    With Sheet1
        .Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet2
        .Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet3
        .Range("BO2:CC2").Value = Application.Transpose(.Range("O5:O19").Value)

       
    With Sheet1
        .Range("BO8:CC8").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet2
        .Range("BO8:CC8").Value = Application.Transpose(.Range("O5:O19").Value)
    End With
    With Sheet3
        .Range("BO8:CC8").Value = Application.Transpose(.Range("O5:O19").Value)
  
  

    Application.OnTime Now + TimeValue("00:05:00"), "test"

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

 

[johnnyL]

Application.Wait (Now + TimeValue("0:00:20"))

 

[John_w]

Application.Wait will lock Excel.

Better with the Timer function and a DoEvents loop so that the Excel UI stays responsive:

    Dim timeout As Single
    timeout = Timer + 20
    Do
        DoEvents
    Loop Until Timer > timeout

Note - a bit more code is needed to handle Timer crossing midnight.

 

[Marc L]

Note - a bit more code is needed to handle Timer crossing midnight.

Sub Pause(Optional P! = 0.01)
     D! = Timer:   F! = D + P:   DoEvents
    While Timer < F
       If Timer < D Then F = F - 86400: D = 0
    Wend
End Sub

 

[Marc L]

Or just using a Date variable :​

    Dim D As Date
    D = Now + TimeValue("0:0:20")
    While D > Now:  DoEvents:  Wend

 

[Michael M]

But Excel will still not be accesible while a code is under execution !!!

 

[johnnyL]

Application.Wait will lock Excel.

That is a harsh/misleading statement.

You make it sound like it 'breaks' the script.

In reality, after the 'Wait' time has elapsed, Excel execution continues normally.

 

[ste33uka]

Sub Pause(Optional P! = 0.01)
     D! = Timer:   F! = D + P:   DoEvents
    While Timer < F
       If Timer < D Then F = F - 86400: D = 0
    Wend
End Sub

timer does not need to go past midnight, but adding code in middle of my existing code wont work,
and Application.Wait (Now + TimeValue("0:00:20")) does lock excel.

 

[Marc L]

​

So you must try to clearly elaborate … As usually this kind of code does not need any pause …​

 

[ste33uka]

​

So you must try to clearly elaborate … As usually this kind of code does not need any pause …​

thanks for everyones help, problem solved, will just run to different macros