Auto Save & Version Control for Excel VBA

Overview

Excel VBA Automation

Losing work to a crash or overwriting a critical version are common Excel frustrations. These VBA macros provide automatic timed saving and incremental version numbering, creating a lightweight version control system directly within Excel.

Start_Auto_Save_Now saves at your chosen interval. New_Version parses the filename for a version pattern (e.g., vSN0.05) and increments it by 0.01. Start_Auto_Versioning combines both: it auto-saves AND auto-increments the version number on a timer.

Features

Custom Intervals: Set your preferred save interval via an input box, from every minute to every hour
Smart Version Parsing: Uses regex to find version patterns like vSN0.01 in the filename and increments automatically
Initials Prefix: Version numbers include a two-letter prefix (e.g., SN for Sam Ngo) to track who created each version
Graceful Fallback: If no version pattern exists in the filename, it appends one automatically on the first versioned save

The VBA Code

Sub Start_Auto_Save_Now()
    Dim Usr_Inp As String
    Usr_Inp = InputBox("Enter save interval (hh:mm:ss)", "AutoSave Interval", "00:01:00")
    If Usr_Inp = "" Then MsgBox "Auto Save not started": Exit Sub
    
    ActiveWorkbook.Save
    Application.OnTime Now + TimeValue(Usr_Inp), "Auto_Save_Now"
End Sub

Sub Auto_Save_Now()
    ActiveWorkbook.Save
    Application.OnTime Now + TimeValue(Usr_Inp), "Auto_Save_Now"
End Sub

Sub Stop_Auto_Save()
    Application.OnTime EarliestTime:=Tme_One, Procedure:="Auto_Save_Now", Schedule:=False
    MsgBox "Auto Save cancelled"
End Sub

Sub New_Version()
    Dim Wkb As Workbook: Set Wkb = ActiveWorkbook
    If Wkb Is Nothing Then MsgBox "No workbook found.": Exit Sub
    
    Dim Wkb_Nam As String: Wkb_Nam = Wkb.Name
    
    ' Parse version pattern: vXX0.00 (e.g., vSN0.01)
    Dim Reg_Obj As Object: Set Reg_Obj = CreateObject("VBScript.RegExp")
    Reg_Obj.Pattern = "v([A-Z]{2})(\d+\.\d+)"
    
    If Reg_Obj.Test(Wkb_Nam) Then
        Dim Mat_Obj As Object: Set Mat_Obj = Reg_Obj.Execute(Wkb_Nam)(0)
        Dim Ini_Val As String: Ini_Val = Mat_Obj.SubMatches(0)
        Dim Ver_Val As String: Ver_Val = Mat_Obj.SubMatches(1)
        Dim New_Ver As String: New_Ver = Format(CDbl(Ver_Val) + 0.01, "#0.00")
        
        Dim Lft_Str As String: Lft_Str = Left(Wkb_Nam, Mat_Obj.FirstIndex)
        Dim Rgt_Str As String: Rgt_Str = Mid(Wkb_Nam, Mat_Obj.FirstIndex + Len(Mat_Obj.Value) + 1)
        
        Wkb.SaveAs Wkb.Path & "/" & Lft_Str & "v" & Ini_Val & New_Ver & Rgt_Str
    Else
        ' No version pattern found - add one
        Dim Base_Nam As String: Base_Nam = Left(Wkb_Nam, InStrRev(Wkb_Nam, ".") - 1)
        Dim Ext_Nam As String: Ext_Nam = Mid(Wkb_Nam, InStrRev(Wkb_Nam, "."))
        Wkb.SaveAs Wkb.Path & "/" & Base_Nam & " vSN0.01" & Ext_Nam
    End If
End Sub

Sub Start_Auto_Versioning()
    ' Combines auto-save with auto-increment versioning
    Dim Usr_Inp As String
    Usr_Inp = InputBox("Enter version interval (hh:mm:ss)", "Auto Version", "00:45:00")
    If Usr_Inp = "" Then Exit Sub
    
    ' Saves and increments version number on each interval
    New_Version
    Application.OnTime Now + TimeValue(Usr_Inp), "Auto_New_Version"
End Sub

Final Thoughts

This lightweight version control is perfect for environments where Git is not practical for Excel files. The automatic versioning creates a clear audit trail, and the timed saves protect against data loss from crashes or accidental closures.