Implement Version Control Systems With Excel VBA

Implementing a Version Control System (VCS) in Excel using VBA can be a useful technique for managing the versions of your Excel workbooks, tracking changes, and even restoring previous versions of your documents. While Excel does not natively support VCS like Git, it is possible to create a simple version control system using VBA to track changes and manage different versions of your workbook.

Key Concepts of Version Control

1. Version Tracking: A version control system allows you to save and track changes made to the workbook, and provides a way to « rollback » to previous versions if necessary.
2. Versioning by Saving Copies: Every time a change is made, we create a copy of the workbook or a snapshot with a new version number.
3. Version Descriptions: To keep track of what changes were made, we can use a description for each version.
4. Automatic Saving: The system will automatically create a new version each time certain changes are made to the workbook.

Step-by-Step Explanation

1. Saving a Workbook as a New Version:
   • You will need to store versions of the workbook in a predefined folder.
   • Each new version will be saved with a unique identifier, like a timestamp or incremental version number.
2. Creating a Version Log:
   • A log will be maintained in a hidden sheet or external file to track the versions, the date they were created, and any descriptions provided by the user.
3. Rollback to Previous Versions:
   • We will allow the user to load previous versions by opening the saved versions.

VBA Code Implementation

Below is a basic implementation of version control in Excel using VBA:

1. Setup Workbook and Version Control Folder

First, create a folder on your system where the versioned workbooks will be stored (e.g., C:\ExcelVersions\). This folder will contain all the saved versions of the workbook.

2. Create a Hidden Sheet for Version Log

You should add a hidden sheet in your Excel workbook where the version log will be stored. This log will contain information such as:

• Version Number
• Date
• Description of Changes

3. VBA Code to Implement Version Control

This is a detailed VBA script for version control:

Sub SaveNewVersion()
    Dim versionFolder As String
    Dim versionNumber As Long
    Dim versionDescription As String
    Dim versionFileName As String
    Dim versionLog As Worksheet
    Dim versionRow As Long
    Dim versionDate As String
    Dim currentWorkbook As Workbook
    ' Initialize variables
    versionFolder = "C:\ExcelVersions\" ' Path to store versioned files
    versionDate = Format(Now, "yyyy-mm-dd_hhmmss")
    Set currentWorkbook = ThisWorkbook
    versionDescription = InputBox("Enter a brief description of this version:", "Version Description")
    ' Check if the version folder exists
    If Dir(versionFolder, vbDirectory) = "" Then
        MsgBox "Version control folder does not exist. Please create it and try again."
        Exit Sub
    End If
    ' Find the next version number (based on the number of files in the version folder)
    versionNumber = GetNextVersionNumber(versionFolder)
    ' Construct the version file name (e.g., "Workbook_v1_2023-03-28_120500.xlsx")
    versionFileName = "Workbook_v" & versionNumber & "_" & versionDate & ".xlsx
    ' Save the current workbook as a new version
    currentWorkbook.SaveCopyAs versionFolder & versionFileName
    ' Log the new version in the version log sheet (create if it doesn't exist)
    On Error Resume Next
    Set versionLog = currentWorkbook.Sheets("VersionLog")
    If versionLog Is Nothing Then
        Set versionLog = currentWorkbook.Sheets.Add
        versionLog.Name = "VersionLog"
        versionLog.Visible = xlSheetVeryHidden
        versionLog.Cells(1, 1).Value = "Version Number"
        versionLog.Cells(1, 2).Value = "Date"
        versionLog.Cells(1, 3).Value = "Description"
    End If
    On Error GoTo 0
    ' Find the next empty row in the version log
    versionRow = versionLog.Cells(versionLog.Rows.Count, 1).End(xlUp).Row + 1
    ' Write the version information into the log
    versionLog.Cells(versionRow, 1).Value = versionNumber
    versionLog.Cells(versionRow, 2).Value = versionDate
    versionLog.Cells(versionRow, 3).Value = versionDescription
    MsgBox "Version " & versionNumber & " saved successfully!"
End Sub
Function GetNextVersionNumber(versionFolder As String) As Long
    Dim fileName As String
    Dim versionCounter As Long
    Dim versionNumber As Long
    Dim versionPrefix As String
    Dim versionSuffix As String
    versionCounter = 0
    versionPrefix = "Workbook_v"
    ' Loop through files in the version folder
    fileName = Dir(versionFolder & "*.xlsx")
    Do While fileName <> ""
        ' Extract the version number from the file name (e.g., "Workbook_v1_2023-03-28_120500.xlsx")
        If InStr(fileName, versionPrefix) > 0 Then
            versionNumber = GetVersionFromFileName(fileName, versionPrefix)
            If versionNumber > versionCounter Then
                versionCounter = versionNumber
            End If
        End If
        fileName = Dir
    Loop
    ' Return the next version number
    GetNextVersionNumber = versionCounter + 1
End Function
Function GetVersionFromFileName(fileName As String, prefix As String) As Long
    Dim versionStr As String
    Dim versionNum As Long
    versionStr = Mid(fileName, Len(prefix) + 1, InStr(Len(prefix) + 1, fileName, "_") - Len(prefix) - 1)
    versionNum = CLng(versionStr)
    GetVersionFromFileName = versionNum
End Function
Sub RollbackVersion()
    Dim versionFolder As String
    Dim versionLog As Worksheet
    Dim versionRow As Long
    Dim versionToRollback As Long
    Dim versionFileName As String
    Dim currentWorkbook As Workbook
    Dim newWorkbook As Workbook
    ' Initialize variables
    versionFolder = "C:\ExcelVersions\" ' Path to store versioned files
    Set currentWorkbook = ThisWorkbook
    Set versionLog = currentWorkbook.Sheets("VersionLog")
    ' Prompt user to select a version to roll back to
    versionToRollback = InputBox("Enter the version number to roll back to:", "Rollback Version")
    ' Find the version in the log
    versionRow = 0
    For i = 2 To versionLog.Cells(versionLog.Rows.Count, 1).End(xlUp).Row
        If versionLog.Cells(i, 1).Value = versionToRollback Then
            versionRow = i
            Exit For
        End If
    Next i
    If versionRow = 0 Then
        MsgBox "Version not found in the log."
        Exit Sub
    End If
    ' Get the file name for the rollback version
    versionFileName = versionLog.Cells(versionRow, 2).Value & "_" & versionToRollback & ".xlsx"
    ' Open the selected version
    Set newWorkbook = Workbooks.Open(versionFolder & versionFileName)
    newWorkbook.Activate
    MsgBox "You have successfully rolled back to version " & versionToRollback & "!"
End Sub

How This Code Works

1. SaveNewVersion:
   • This subroutine saves the current workbook as a new version in the specified folder.
   • It assigns a version number based on existing files and appends a timestamp to the file name.
   • The version information is then logged into a hidden sheet for tracking.
2. GetNextVersionNumber:
   • This function checks the files in the version control folder and returns the next version number based on the highest version available.
3. GetVersionFromFileName:
   • This helper function extracts the version number from the file name.
4. RollbackVersion:
   • This subroutine allows the user to rollback to a previous version by opening the saved version from the version control folder.

Conclusion

This VBA implementation offers a basic version control system for Excel. It allows users to save and track different versions of their workbooks, and even roll back to previous versions when necessary. While this is a simple version control system, it can be enhanced further with features such as automated backups, user prompts for confirming version descriptions, or better error handling.