Versioning Excel files with Excel VBA

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.

Other VBA File resources

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.

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.

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:

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:

This is the final effect:

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

Related posts:

Analyst Cave