apply a pause in an Excel VBA macro Solved

Question

Hello,

I'm looking for a way to pause the execution of my macro in VBA Excel for a few seconds, and to do this invisibly for the user.
If anyone has a snippet of code, that would be great!!!

I'm using VBA Excel 2007

Thank you

Configuration: Windows XP / Internet Explorer 7.0

Woody · Accepted Answer

I had the same question, and I found it even simpler:

Sub Wait() ' Wait 5 seconds Application.Wait Time + TimeSerial(0, 0, 5) ' Continue after the pause End Sub

replace the "5" with the desired number of seconds

pijaku · Answer

Hello,

To make a pause, you can indeed use Sleep by declaring the API at the beginning of the module:

Declare Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)

Then call it in your procedure:

Sleep 1000 'Sleep is measured in milliseconds. So Sleep 1000 will wait for 1 second...

You can also use Wait. Wait measures time in seconds. Here's a Microsoft example of waiting for 10 seconds [According to Microsoft: This example pauses a running macro for approximately 10 seconds], the approximately really amuses me…:

newHour = Hour(Now()) newMinute = Minute(Now()) newSecond = Second(Now()) + 10 waitTime = TimeSerial(newHour, newMinute, newSecond) Application.Wait waitTime

There’s also Timer... See this example from Mister lermitte222...

Timer and wait count in seconds, sleep counts in milliseconds.
With timer you can decide to perform a repetitive task every "x" units of time, with Wait or Sleep, it’s your macro that waits before continuing its execution. [Sources: ouskel'n'or]
Best regards,
Franck P

Emmanuelagen · Answer

Hello,
Thank you for this information, it was very helpful to me.
I have a question related to this topic. I have inserted this waiting time in my macro linked to a button, but I would like the time not to be limited and for the macro to resume on the next mouse click...
Thank you for your help
Best regards
Emmanuel

benaflak · Answer

Dim PauseTime, Start, Finish, TotalTime If (MsgBox("Appuyez sur Oui pour faire une pause de 5 secondes", 4)) = vbYes Then PauseTime = 5 ' Définir la durée. Start = Timer ' Définir l'heure de début. Do While Timer < Start + PauseTime DoEvents ' Céder à d'autres processus. Loop Finish = Timer ' Définir l'heure de fin. TotalTime = Finish - Start ' Calculer le temps total. MsgBox "Mis en pause pendant " & TotalTime & " secondes" Else End End If

Apply a pause in an Excel VBA macro

4 réponses

Windows spotlight is not working.

Masked destination layer

Samsung j5 unable to answer

Scam of the suvedene highway

File name too long

No sound with ps3 and hdmi

Wake up from sleep mode in windows 10.

Internet connection problem with my ipad

Free box: remote control broken

Suspicious number text message