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.

ArrowStartTimer

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

ArrowStartingMessage

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.

ArrowAfterRunningMacro

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

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube