Tag Archives: versioning

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!

versioning excel

Versioning Excel files with Excel VBA

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

Usually when developing Excel solutions you want to version you file often to prevent data loss due to the application crashing etc. You will probably also want to keep the older versions of you files to be able to go back and recover any previously working code. When you do this once or twice in a while this is no issue. But when you are making significant changes in a short amount of time saving new versions is a time-consuming task. Excel versioning is therefore something many deem useful.

That’s why I made myself a very simple VBA method for automatically saving the current Excel xlsm file as a new version while keeping the previous versions of the file. The macro increments the current file version. It is best to set a keyboard shortcut to the macro to save time.

Excel VBA Versioning
Excel VBA Versioning

Versioning Excel Code

The following code will save the ActiveWorkbook as a new Workbook while appending the version number by 1 in the format “_vXXX” where XXX is the version number. The versioning macro will maintain the file extension.

Sub SaveNewVersion()
    Dim fileName As String, index As Long, ext As String
    arr = Split(ActiveWorkbook.Name, ".")
    ext = arr(UBound(arr))
    If InStr(ActiveWorkbook.Name, "_v") = 0 Then
        
        fileName = ActiveWorkbook.Path & "" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1) & "_v1." & ext
        ActiveWorkbook.SaveAs (fileName)
    Else
        index = CInt(Split(Right(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - InStr(ActiveWorkbook.Name, "_v") - 1), ".")(0))
        index = index + 1
        fileName = ActiveWorkbook.Path & "" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, "_v") - 1) & "_v" & index & "." & ext
    End If
    ActiveWorkbook.SaveAs (fileName)
End Sub

Download

You can also download the file as a bas file:


The module sets the macro as a CTRL+SHIFT+S shortcut as having this line of code:

Attribute SaveNewVersion.VB_ProcData.VB_Invoke_Func = "Sn14"

Setting up the Versioning Excel Macro

Keyboard shortcut

As mentioned above the macro in the download section is setup by default for CTRL+SHIFT+S shortcut. However, in case you want to change the shortcut. Simply go to the DEVELOPER ribbon and select Macros. Next select the SaveNewVersion macro and click Options.... This will prompt you for a new keyboard shortcut.

Set Excel Macro Shortcut
Set Excel Macro Shortcut

Quick Access Toolbar

Why remember a keyboard shortcut when you can add a neat icon to your Quick Access Toolbar in Excel.

Open the Quick Access Toolbar

Go to File, Options and open the Quick Access Toolbar.

Add versioning VBA macro to the Quick Access Toolbar

Proceed as shown below to Add the SaveNewVersion macro to the Quick Access Toolbar:

Quick Access Toolbar: Add macro
Quick Access Toolbar: Add macro

Optional: Modify the icon

Why stick to a default macro icon when we can make it more pleasant to the eye? Click on the SaveNewVersion macro and hit Modify. Next select a new icon from this window:

Save New Version: Select a new icon
Save New Version: Select a new icon

This is the final effect:
Quick Access Toolbar: Versioning Excel
Quick Access Toolbar: Versioning Excel

Simply hit the icon to save a new version of your Excel file! Remember to save the file in XLS/XLSM/ or XLSB format.

Installing as an Excel AddIn

The above will add the versioning feature to all your Workbooks as long as your Excel file with the SaveNewVersion macro is not moved or deleted!. I strongly recommend that instead you include this file into the AddIns folder before configuring this shortcut.

Save the XLSM file as AddIn

First save the file in XLA or XLAM format, as an Excel AddIn.

Save the file in Microsoft Excel AddIn directory

Save the file in the following directory for it to open automatically on startup:

C:/Users/USERNAME/AppData/Roaming/Microsoft/AddIns