Execute Command/Sub-Procedure Using VBA Timer

In this article, we will create a macro to call another macro or execute a command after defined intervals of time.

To execute the macro, click on the “Start Timer” button.

On running the macro, “TimerMsg” macro will display a message box.

When a user clicks on the “OK” button, three seconds later, “MsgProcedure” macro will be called. “MsgProcedure” macro will display a message box.

Logic explanation

In this example, we have created two macros “TimerMsg” and “MsgProcedure”. “TimerMsg” macro displays an information message and calls “MsgProcedure” after waiting for three seconds, as defined in the code.

Code explanation

Application.OnTime Method

Application.OnTime method is used to schedule a procedure to run at defined intervals of time.

Syntax

Application .OnTime Time, Procedure_name

Time specifies the time interval at which procedure should be run.

Procedure_name defines the name of the procedure.

We have used Application.onTime method to schedule “MsgProcedure” macro.

Please follow below for the code

Option Explicit

Sub TimerMsg()

'Declaring Date variable
Dim AlertTime As Date

'Displaying message box at the start of the timer
MsgBox "The alarm will go off in 3 seconds!"

'Setting timer time for 3 seconds after the activation time
AlertTime = Now + TimeValue("00:00:03")

'Activating the timer and running the MsgProcedure at the end of the timer
Application.OnTime AlertTime, "MsgProcedure"

End Sub

Sub MsgProcedure()
'Sample procedure created for timer example

MsgBox "Three Seconds is up!"

End Sub

If you liked this blog, share it with your friends on Facebook. Also, you can follow us on Twitter and Facebook.

We would love to hear from you, do let us know how we can improve our work and make it better for you. Write to us at info@exceltip.com