Ok, everybody need this. Every single programmer that I’ve known once in his miserable life needed to do this in your programs. Alright, this is too dramatic, but it’s true. The problem is, in VBA, there is no easy going way to find a “how to do this”, like it is in other languages. But don’t worry. I’m here to save your lives, or something like that. Ok, too dramatic again.

With VBA, there are a couple of ways to do this.

First Method: Use an empty loop For… Next

The problem here is that you don’t have any control on how long it will take/sleep/wait/whatever. It’s just a way to make your program do nothing for a while. The code below runs for a while, given to the user the impression of be waiting.

1
2
3
4

Sub MyDelayMacro
 For iCount = 1 to 1000
 Next iCount
End Sub

Second Method: Use an API to call the Sleep method

Yes, there a Sleep method which can be called from VBA! This causes the same effect as the other programs, holding the process of the program for a defined amount of time, in milliseconds.

The kernel32 lib contains this method, so, you need to declare it in the top module to call it.

1
2

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

To call the Sleep method::

1
2
3

Sub Sleep()
 Sleep 1000   'Faz o código esperar por 1 segundo
 End Sub

Third Method: Use the OnTime Method

Actually, it’s my favourite. The OnTime method is embedded in VBA and the programmers are often used to it. The syntax is a bit weird, but it works!

It requires the name of the macro/method which will be called after some time, which is the first argument of the method. So, as it seems to be, you will need two macros. The first one will activate the OnTime, while the second will be called by the OnTime.

In the code below, the “MyMainMacro” activate the OnTime to call “MyDelayMacro” after 15 seconds.

1
2
3
4
5
6
7
8

Sub MyMainMacro()
 ' Pausa por 15 segundos.
 Application.OnTime Now + TimeValue("00:00:15"), "MyDelayMacro"
 End Sub
Public Sub MyDelayMacro()
 ' Macro executada sob o agendamento.
 MsgBox "Esta macro foi executada após 15 segundos."
 End Sub

Fourth Method: Call the Application.Wait method

It’s similar to Sleep, but instead of send the amount of seconds as an argument, you need to build a TimeValue variable to send as a parameter. You need to think a bit different to use, because the program will wait to the time you have defined. Let’s see take a look at the code:

1
2
3
4
5
6
7

Sub MyWaitMacro()
newHour = Hour(Now())
newMinute = Minute(Now())
newSecond = Second(Now()) + 3
waitTime = TimeSerial(newHour, newMinute, newSecond)
Application.Wait waitTime
End Sub

To read more about the OnTime method, check this post: https://www.tomasvasquez.com.br/blog/microsoft-office/vba-agendando-a-execucao-de-macros-com-a-funcao-ontime

Enjoy!