vba application ontime

Excel VBA Application OnTime – Scheduling VBA Procedures

1 Star2 Stars3 Stars4 Stars5 Stars (2 votes, average: 4.50 out of 5)
Loading...

One of the many useful features of Excel and other MS Office applications, of which little users know of is the VBA OnTime event. It allows you to execute any Excel VBA procedure after the specified amount of time (delay). Using it recursively allows you to create a recurring / repeating event which lets you schedule periodic VBA Macros to run when working on Excel Workbooks.

Want to save your Excel file as a new version automatically? Read my post on versioning Excel files using VBA

Application OnTime Syntax

The syntax of the VBA Application OnTime Event:

Parameters

EarliestTime
The time (Date variable) when the Procedure is to be executed

Procedure
A string with the name of the Procedure which is to be executed

LatestTime
Optional. This is the latest time when the Procedure should be executed. This allows Excel for some flexibility when prioritizing Procedure execution together with other Excel (or other MS Office applications) Events such as Cut, Copy or Find. It makes sense to use this variable when your user is working on the file, so as not to interrupt any activities

Schedule
Optional. Defaults to True. If True schedules a new OnTime procedure. If False clears a previously set procedure. Useful when you want to cancel a previously scheduled Procedure

Schedule a Procedure

The code below will schedule the HelloWorld Sub to run in 1 seconds.

Wait 1 second and expect a Message Box to open with the message “Hello World!”.

Cancelling Application OnTime

You can also use the Application OnTime function to Cancel Scheduled Procedures.

Example 1: Automatic worksheet recalculate using OnTime

The Timer can come in handy when we need to refresh our worksheet(s) periodically and do not want to do this manually. See the example below:

Let us add this procedure in any VBA module within the workbook:

Next add this to the Workbook in the “Microsoft Excel Objects” section of the VBA project:

That is it! Once you open the workbook the whole workbook will be recalculated every 10 seconds. In order to stop it simply close the workbook or debug the Refresh procedure.

Example 2: Automatic workbook versioning using OnTime

Working on a large Excel solution and afraid that you workbook will crash? Want to have a separate backup copy of your file every now and then. Why not use automatic file versioning then?

Add this code to any VBA module:

Next replace the Refresh function with this:

Voila! Your workbook will be saved as a new version every 10 minutes. Great isn’t? There are probably a ton of ways you can further tweak this to work better e.g. add a pop-up asking to confirm the creation of the next version etc.

Hope that this will prove useful to you! Let me know!

Related Posts

2 thoughts on “Excel VBA Application OnTime – Scheduling VBA Procedures”

  1. Dear Tom,Thank you for your answer,First of all : sorry for my lacinoc post .I know that your code works i use it to update another pp table, i just have problems with this oneHere are details :I have excel 2010 32 bit + PowerPivot V2 (polish versions)The error is :-2147467259 (80004005)’ , The message could be translated (from polish) as something like that : The operation has been cancelled beacuse execution of other operation in the transaction has failed don’t know the exact english text I try to update a single table , beacuse when i update all tables(without specyfing the name of the table in the code) i recive some memory error (message about considering switching my excel to 64bit or adding some RAM)As i’ve mentioned above the code works with other table (same workbook)If i try tu refresh manually all tables i also receive memory error, if i refresh single table manually it works.Sources for both tables are excel files the working one is 15MB file, the other is 45MBThe 45MB file has about 160k rows, but i try to import about 60k rows (by column filters in PP window ->table properties)I start to think it can be a memory issue, but it’s strange that it works in manual mode and doesn’t work when using code.Thank you,Marcin

    1. Hi Marcin,

      it seems you ran into a memory issue. See here on some of the memory limits in Excel: https://support.office.com/en-za/article/Excel-specifications-and-limits-ca36e2dc-1f09-4620-b726-67c00b05040f.

      Either try splitting your Excel file, write a macro to refresh them one-by-one (if you noticed you can run 1 at once with no error), try uninstalling some Add-Ins, or indeed try switching to a 64bit version. Otherwise I would try raising this issue on StackOverflow.

Leave a Reply