Cat On A Spreadsheet

From Personal Macros to a Structured Excel Add-In

Most Excel automation starts the same way: you record a macro, you improve it, you copy it into another workbook. Over time, those macros accumulate. Some live in PERSONAL.XLSB. Others sit inside operational workbooks. A few get emailed around the company whenever someone needs them. Eventually you realise something uncomfortable: your automation has become useful enough that other people rely on it — but it has no structure, no versioning, and no clear way to distribute updates.

This is the point where many Excel developers discover the .xlam add-in format.

An Excel add-in allows you to centralise automation into a single managed file that loads into Excel at startup. The macros become globally available without being embedded inside operational workbooks. Instead of copying code between files, you maintain a single source of truth.

In this series we will take the perspective of a single developer building an automation library that can later be distributed to colleagues or deployed inside an organisation.

By the end of the series you will have:

a structured VBA add-in project
a version-controlled codebase
a repeatable export workflow for Git
a signed add-in ready for enterprise deployment

But the first step is simply creating the foundation.

Creating the Add-In Project

Start with a completely new Excel workbook. Save it somewhere dedicated to development. A simple structure like this works well:

Excel-Automation Addin Source Builds

The workbook we are about to create will eventually become the master automation library.

Now save the workbook as an add-in.

File → Save As → Excel Add-In (*.xlam)

Give it a clear name. For example, AutomationTools.xlam

When you save a workbook as .xlam, Excel hides the workbook interface and loads it as a background component. The macros remain available but the worksheets are no longer visible. This is exactly what we want: the file becomes a code container rather than a working workbook.

Opening the Add-In for Development

Once saved, Excel will automatically load the add-in.

To edit it again:

Open the VBA editor (ALT + F11)
Locate the project named VBAProject (AutomationTools.xlam)

If the project is locked or not visible, you can reopen it manually through Developer → Excel Add-Ins → Browse. Select the .xlam file and load it.

From now on, this file becomes the home of all shared automation code.

Designing the First Module Structure

Before writing macros, it's worth creating a minimal structure.

Inside the VBA editor create two modules.

modPublic

modInternal

The idea is simple:

modPublic contains procedures meant to be called by users or other workbooks.

modInternal contains helper procedures that support the main functionality but are not intended to be called directly.

This separation becomes extremely valuable as the project grows. It creates a clear distinction between the public interface and the internal implementation.

For now we will add a very simple macro that proves the add-in works.

Adding a Test Procedure

Inside modPublic, add a small procedure.

Public Sub TestAddin() MsgBox "The AutomationTools add-in is loaded." End Sub

Save the project and return to Excel. Press Alt+F8 and you should see TestAddin listed as an available macro. Run it.

If the message box appears, the add-in is working exactly as expected. The macro is running from the add-in, not from the workbook you currently have open.

This is the core behaviour that makes add-ins so powerful.

Making the Add-In Load Automatically

To make the add-in available every time Excel starts:

Go toDeveloper → Excel Add-Ins
Click Browse
Select your .xlam file
Ensure the checkbox next to it is enabled

From this point forward, the automation library loads whenever Excel launches. You now have a persistent macro environment independent of any workbook.

Why This Structure Matters

Even with a single macro, the benefits are already visible. Your automation is no longer trapped inside a workbook. Instead:

workbooks remain clean and macro-free
automation lives in one controlled location
updates happen in a single file
new functionality becomes instantly available everywhere

This approach scales extremely well. A single developer can maintain dozens of reusable utilities inside one add-in without polluting operational spreadsheets.

But there is still a major limitation. Excel stores VBA inside binary files, which means version control systems like Git cannot track code changes properly. Two developers modifying a macro will overwrite each other, and even a single developer loses meaningful history.

In the next article we solve that problem by exporting every VBA module to text files, allowing the entire add-in to be managed with proper version control. Once that workflow is in place, your Excel automation stops behaving like a collection of macros and starts behaving like a software project.