Cat On A Spreadsheet

Automating the Build and Release Process for Your VBA Add-In

By this stage in the series, the nature of your Excel solution has shifted quite decisively away from the familiar pattern of scattered macros and toward something that more closely resembles a managed system. The add-in now serves as the central execution layer, your source code is maintained outside of Excel in a form that can be properly version controlled, and your deployment model ensures that users are always working against a single, authoritative version.

What remains, however, is the process by which a release is actually produced. Up to this point, that process has likely retained a degree of manual intervention, requiring you to export modules, confirm that the correct code is loaded, apply a signature, and copy the result into a shared location. While each of these steps is straightforward, the sequence as a whole introduces an unnecessary dependency on memory and consistency, both of which tend to degrade over time.

Rather than attempting to enforce discipline around a manual process, it is far more effective to remove the manual element entirely and replace it with something deterministic. The objective, therefore, is not simply to automate individual steps, but to establish a controlled build process that consistently produces a clean, reliable output.

Separating Build Responsibility from the Add-In

A common instinct at this point is to embed the build logic directly within the add-in itself, effectively allowing it to reconstruct its own codebase and produce a release from within. Although this may appear efficient, it introduces a fundamental instability, because VBA is not designed to safely modify a project while executing code from within that same project. Attempting to rebuild an add-in in situ creates a fragile dependency on execution state, where modules may be locked, partially loaded, or otherwise resistant to modification.

A more robust approach is to step outside that constraint entirely by introducing a separate build controller, implemented as a standalone workbook whose sole purpose is to assemble and prepare the add-in for release. In doing so, you establish a clear separation between the system being built and the process that builds it, thereby eliminating the risks associated with self-modifying code.

This controller does not participate in the business logic of your solution, nor does it expose any functionality to end users. Instead, it operates as an orchestration layer, responsible for opening the add-in, reconstructing its contents from source-controlled files, producing a versioned output, and preparing that output for signing and deployment.

Establishing the Controller

The controller itself can be created as a macro-enabled workbook, for example:

BuildController.xlsm

Once created, this workbook becomes the entry point for your build process. From here, you initiate the sequence that transforms your source files into a deployable add-in, without requiring any interaction with the add-in’s internal execution context.

The process begins by opening the target add-in programmatically, thereby establishing a handle through which its contents can be manipulated.

  Public Function OpenTargetAddin(path As String) As Workbook
 
      Set OpenTargetAddin = Workbooks.Open(path)
 
 End Function

At this point, the add-in exists purely as a data structure from the perspective of the controller, rather than as an actively executing project, which is precisely what allows the subsequent steps to proceed without interference.

Rebuilding the Project from Source

With the target add-in open, the first meaningful step in the build process is to remove its existing modules, thereby ensuring that the rebuild begins from a known, clean state. Because this operation is being performed externally, there is no need to account for executing code or to selectively exclude modules; the project can be cleared in a straightforward and deterministic manner.

  Public Sub ClearModules(targetWb As Workbook)
 
      Dim i As Long
 
      Dim comp As Object
 
      With targetWb.VBProject.VBComponents
 
          For i = .Count To 1 Step -1
 
              Set comp = .Item(i)
 
              If comp.Type = 1 Or comp.Type = 2 Or comp.Type = 3 Then
 
                  .Remove comp
 
              End If
 
          Next i
 
      End With
 
 End Sub

Once the project has been cleared, it can be reconstructed directly from the source-controlled files, ensuring that the resulting add-in reflects exactly what exists in your repository rather than whatever state may have existed during development.

  Public Sub ImportModules(targetWb As Workbook, sourcePath As String)
 
      Dim fileName As String
 
      fileName = Dir(sourcePath & "*.bas")
 
      Do While fileName <> ""
 
          targetWb.VBProject.VBComponents.Import sourcePath & fileName
 
          fileName = Dir
 
      Loop
 
 End Sub

Although this example focuses on standard modules, the same pattern can be extended to class modules and user forms, resulting in a complete reconstruction of the project.

Producing a Versioned Build Artifact

With the add-in now rebuilt from source, the next step is to produce a clean, versioned output. Rather than overwriting the original file, the controller generates a separate build artifact, typically named using a timestamp or version identifier that reflects when the build was created.

  Public Sub SaveBuild(targetWb As Workbook, buildPath As String)
 
      Dim buildName As String
 
      buildName = "AutomationTools_" & Format(Now, "yyyy.mm.dd.hhmm") & ".xlam"
 
      targetWb.SaveCopyAs buildPath & buildName
 
 End Sub

This approach not only preserves the integrity of your development file, but also provides a clear history of builds that can be referenced or rolled back if necessary.

Completing the Build Process

At this stage, the add-in is structurally complete and ready to be signed. Because digital signatures are tied to the exact contents of the file, signing remains an explicit step in the process, ensuring that each release is both intentional and verifiable.

Once signed, the build can be deployed by copying it into the central distribution location, where it replaces the previous version and becomes available to users the next time Excel is launched.

To bring these steps together, the controller exposes a single procedure that orchestrates the entire process from start to finish.

  Public Sub BuildAddin()
 
      Dim target As Workbook
 
      Set target = OpenTargetAddin("C:\\Excel-Automation\\Addin\\AutomationTools.xlam")
 
      Call ClearModules(target)
 
      Call ImportModules(target, "C:\\Excel-Automation\\Source\\")
 
      Call SaveBuild(target, "C:\\Excel-Automation\\Builds\\")
 
      target.Close SaveChanges:=True
 
      MsgBox "Build complete. Please sign and deploy."
 
 End Sub

Running this procedure produces a clean, consistent build that is fully aligned with your source-controlled code, without requiring any manual intervention beyond the deliberate act of signing.

Establishing a Reliable System

What this approach ultimately provides is not just automation, but clarity. By externalising the build process, you eliminate hidden dependencies and remove the ambiguity that comes from working within a live project. The add-in becomes a product of the process rather than the environment in which it was developed, and each release becomes a deliberate, reproducible outcome. In doing so, you complete the transition from ad hoc macro development to a structured delivery model, one in which Excel is no longer merely a host for automation, but a platform through which that automation is consistently built, validated, and distributed.