Cat On A Spreadsheet

Version Control for VBA Workbooks and Code Promotion Between Environments

One of the biggest reasons VBA projects fail at scale isn’t performance or complexity — it’s change management. A macro works today, someone tweaks it tomorrow, and three weeks later nobody knows which version is running in production, which file is safe to edit, or why a bug “came back” after it was already fixed.

Excel encourages experimentation, but once a workbook becomes a shared tool, experimentation without control quickly turns into chaos. At that point, VBA code needs to be treated like software, not a personal macro playground. That means versioning, controlled promotion between environments, and a clear separation between development and production.

This post walks through how to implement a practical, battle-tested version control strategy for VBA projects — one that works within Excel’s limitations but still gives you confidence, traceability, and safe deployments.

Why Copying Workbooks Is Not Version Control

Many teams attempt version control by copying files and appending dates or initials to the filename. This approach breaks down immediately once more than one person is involved. There is no single source of truth, no reliable history, and no way to tell which changes were intentional and which were accidental. Real version control answers three questions clearly:what changed, when it changed, and why it changed.

For VBA projects, the challenge is that code lives inside binary files, which traditional version control systems cannot diff meaningfully. The solution is not to abandon version control, but to extract the code from the workbook and treat it as first-class source code.

Separating Code From the Workbook

A maintainable VBA system treats the workbook as a container, not as the authoritative source of code. The authoritative source lives outside Excel, in exported .bas, .cls, and .frm files. These files can be versioned, reviewed, compared, and promoted between environments safely. The workbook itself becomes something closer to a compiled artifact. It contains the code at runtime, but it is not where development happens.

This mental shift is critical. Once you adopt it, everything else becomes simpler.

Exporting VBA Modules Automatically

Manual exporting is error-prone and easy to forget. A scalable system uses automation to export all modules from the VBA project into a structured folder. This export process becomes part of the development workflow.

Here is a standard module that exports all standard modules, class modules, and forms to disk:

  Public Sub ExportAllModules()
 
      Dim comp As Object
 
      Dim exportPath As String
 
      exportPath = ThisWorkbook.Path & "\VBA_Source\"
 
      If Dir(exportPath, vbDirectory) = "" Then MkDir exportPath
 
      For Each comp In ThisWorkbook.VBProject.VBComponents
 
          Select Case comp.Type
 
              Case 1 ' Standard module
 
              comp.Export exportPath & comp.Name & ".bas"
 
              Case 2 ' Class module
 
              comp.Export exportPath & comp.Name & ".cls"
 
              Case 3 ' UserForm
 
              comp.Export exportPath & comp.Name & ".frm"
 
          End Select
 
      Next comp
 
 End Sub

Once exported, these files can be committed to Git, Subversion, or any source control system your organization uses.

Rebuilding a Workbook From Source

Promotion between environments should never involve copying a user’s working file. Instead, you start with a clean workbook shell and import the code from source. This ensures that no accidental edits, broken references, or corrupted modules make it into the next environment.

Here is the complementary import routine:

  Public Sub ImportAllModules()
 
      Dim file As String
 
      Dim importPath As String
 
      importPath = ThisWorkbook.Path & "\VBA_Source\"
 
      file = Dir(importPath & "*.*")
 
      Do While file <> ""
 
          ThisWorkbook.VBProject.VBComponents.Import importPath & file
 
          file = Dir
 
      Loop
 
 End Sub

The workbook becomes reproducible. If you can rebuild it from source, you can trust it.

Introducing Development, Test, and Production Environments

Once code is externalized, separating environments becomes straightforward. The same source code is promoted through environments in a controlled order.

Development is where code is edited and exported. Test is where code is imported into a clean workbook and exercised against realistic data. Production is built only from approved source, never from a developer’s working file.

Each environment should carry a visible version number, stored in a named cell or hidden sheet, so that users and developers can immediately identify what is running.

  Public Function GetAppVersion() As String
 
      GetAppVersion = ThisWorkbook.Names("AppVersion").RefersToRange.Value
 
 End Function

This version number should match a tag or release number in source control.

Promoting Changes Safely

Promotion should be boring. That is the goal.

A typical promotion flow looks like this: code is modified in development, exported, committed to source control, reviewed if necessary, then imported into a clean test workbook. Only after validation is the same source imported into the production workbook. At no point should users be editing production files. At no point should production workbooks be used as a base for development. This discipline eliminates entire classes of bugs that plague Excel-based systems.

Protecting Production From Accidental Edits

A production workbook should actively defend itself. VBA projects should be locked with a password, sheets protected where appropriate, and developer-only features disabled. You can also add environment checks that prevent risky operations from running in production:

  Public Sub EnsureNotProduction()
 
      If ThisWorkbook.Names("Environment").RefersToRange.Value = "PROD" Then
 
          MsgBox "This operation is disabled in production.", vbCritical
 
          End
 
      End If
 
 End Sub

This prevents “just one quick fix” from becoming tomorrow’s outage.

Dealing With VBA Corruption and Recovery

VBA projects can and do become corrupted. A version-controlled source directory is your insurance policy. If a workbook starts behaving strangely, you don’t debug the corruption — you discard the file and rebuild it from source. This alone saves hours, sometimes days, of troubleshooting.

Why This Matters More Than Any Single Macro

Version control is not about ceremony. It’s about confidence. When you know exactly what code is running, where it came from, and how it got there, you can move faster, refactor aggressively, and support more users without fear. Excel may not be a traditional development platform, but disciplined teams treat VBA with the same respect they give any other production system. When they do, Excel stops being fragile — and starts being reliable.