Excel VBA Timer Event – Run Macro at Specific Time – Automatic Schedule

Here you will learn – How to add Excel VBA Timer Event to run macro automatically at certain time & how to create a Excel timer.

There are 2 different possibilities in here. Are you going to run the schedule the macro to run once or repeatedly.

• Scheduled Macro Run(One Time): VBA run macro at specific time or
• Excel VBA Timer Event (Reccursive): Adding repeated tasks to Excel VBA Timer Event. Set of code has to run repeatedly based on time interval.

Function Used: Both this scenarios use the Excel VBA Timer Event – Application.OnTime

Let us see how this can be achieved using examples:

1. How to Run Excel Macro Automatically at Specific time?

To setup a Excel VBA timer event and schedule a macro to run at particular time, follow these steps.

1. Press Alt+F11 from Excel to open VB editor.
2. Click Menu -> Insert -> Module, to add new module in VBA Project.
3. Copy & add the code in this page.
4. Keep the cursor inside “Add_Schedule_Event_in_VBA()” procedure.
5. Press F5 to activate time event.

Private Sub Excel_VBA_Timer_Event()
    MsgBox "Automatic Scheduled Event: Good Morning"
End Sub

Private Sub Schedule_Run_Macro_Specific_Date_Time()
    'Application.OnTime Schedule_Time,"Sub Procedure Name"
    Application.OnTime VBA.Now + TimeValue("00:00:01"), "VBA_Timer_Event"
End Sub

This will automatically trigger the code, exactly at the scheduled time.

In 5 seconds, a message box will pop-up with the text you have entered in code. This way you don’t want to keep any remainders for certain tasks.

2. Timer in Excel – How to Run Macro Repeatedly at Certain Time Interval

 Adding a vba timer event to a Excel Macro is similar to the scheduling macro.

But just that we need to mention the same procedure name to Application.Ontime function, as in below example.

Private Sub Excel_VBA_Timer_Repeated_Run_Macro()
    ThisWorkbook.Sheets(1).Cells(1, 1) = VBA.Now
    Application.OnTime Now + TimeValue("00:00:01"), "VBA_Timer_Repeated_Event"
End Sub

Copy paste this code into you module and press F5.

Now, go to the Sheet1 on the workbook and you can see that Cell A1 will have current system time and it will refresh every second.

Restrictions:

This VBA timer event will keep on running the macro at certain time interval repeatedly. So, it will hinder the manual tasks that you are performing in any Excel file.

Before executing a timer event, save all your latest work in all the Excel workbook. Also, keep the interval for more than 5 seconds, while you activate this function for first time or while testing.

Timer in Excel & Scheduled VBA Macro

One could execute a VBA code either by using a User Interface (Clicking a command button) or Event Triggered (When user edit a value in cell) or Time Triggered.

Here are some of the Time triggered application uses.

• Scheduled Mail Service: We can combine the functionality of sending personalized email from Excel with Timer and make the Excel or Outlook VBA send email at specific time to set of email ids.
• Quiz: Excel VBA Timers can be used to organize online quiz application, that will complete within certain Time Period.
• Task Monitor: Employees can be asked to enter the task that they are performing for every scheduled time interval. By this, they can utilize their Work Time more efficiently.
• Schedule Tasks: Though Windows OS has the option to add scheduled tasks. with this Excel VBA timer option, you can build your own customized task scheduler. Using Shell and Application.OnTime function, a list of tasks can be executed from Excel at predefined time.

Also Read: Lock, Logoff, Shutdown system at scheduled Time and Balance Work-Life

Web References for More Info:

1. http://msdn.microsoft.com/en-us/library/office/ff196165(v=office.14).aspx
2. http://www.ozgrid.com/forum/showthread.php?t=17162