Excel VBA Scheduled Macro to Run at Specific Time
This blog explains about how to add Excel VBA timer event to run macro automatically at certain time.
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. Lets see the more details about schedule a vba macro to run at a specific time .
- Scheduled Execution: VBA run macro at specific time or
- Repeated Tasks – Excel VBA Timer Event: Set of code has to run repeatedly based on time interval.
Let us see how this can be achieved using examples:
1. Excel VBA Timer Event
To setup a Excel VBA timer event and schedule a macro to run at particular time, follow these steps.
12345678Private Sub Excel_VBA_Timer_Event()MsgBox "Automatic Scheduled Event: Good Morning"End SubPrivate Sub Add_Schedule_Event_in_VBA()'Application.OnTime Schedule_Time,"Sub Procedure Name"Application.OnTime VBA.Now + TimeValue("00:00:01"), "VBA_Timer_Event"End Sub
- Press Alt+F11 from Excel to open VB editor.
- Click Menu -> Insert -> Module, to add new module in VBA Project.
- Copy & add the code in this page.
- Keep the cursor inside “Add_Schedule_Event_in_VBA()” procedure.
- Press F5 to activate time event.
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. VBA Timer in Excel – Run Macro Repeatedly
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.Timer in Excel1234Private Sub VBA_Timer_Repeated_Event()ThisWorkbook.Sheets(1).Cells(1, 1) = VBA.NowApplication.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.
This VBA timer event will keep on executing every second. 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.
Applications of Scheduled Excel Macro
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.
Web References for More Info:
Excel VBA Timer Event – Schedule Macro to Run at specific Time