Excel Macros: A Maintenance Nightmare Waiting to Happen

Somewhere in your business, there’s an Excel file with macros in it. Someone built it years ago. It does something important — generates reports, formats data, calculates pricing, populates templates. Everyone uses it. Nobody understands how it works. And the person who built it? They’ve left, or they’re the only one who can fix it when it breaks, and they’re tired of being the spreadsheet firefighter.

This is one of the most common — and most underestimated — risks in small and mid-size businesses. VBA macros in Excel are genuinely powerful. They automate repetitive tasks, enforce business logic, and can save hours of manual work. But they also become unmaintainable liabilities faster than almost any other business tool.

How Macros Start (And Why They’re So Tempting)

The story is always the same. Someone on the team — usually not a programmer, just someone clever with Excel — realises they’re doing the same task over and over. Copy these cells, paste them there, format this way, email the result. So they record a macro. Then they edit it a bit. Then they learn some VBA, add some logic, and before long they’ve built a genuinely useful tool.

The business loves it. What used to take 45 minutes now takes 10 seconds. The person gets praise, maybe builds a few more macros, and gradually becomes the go-to for spreadsheet automation.

Here’s the problem: none of this was planned. The macro wasn’t designed, documented, or tested. It works because it was built to solve one specific problem in one specific context. The moment that context changes — a new column in the data, a different version of Excel, a new team member who does things slightly differently — the macro breaks.

The Single Point of Failure

This is the biggest risk and the one that causes the most real-world damage.

When one person builds and maintains all the macros, your business has a single point of failure with a human face. If that person goes on holiday, gets sick, changes roles, or leaves the company, every macro they built becomes a black box that nobody can open.

VBA code inside an Excel file is invisible to anyone who doesn’t know to look for it. Most users don’t even know macros exist in the file — they just click the button and things happen. When the button stops working, they’re completely helpless.

And VBA code written by non-programmers is typically:

• Undocumented — no comments explaining what each section does or why
• Fragile — relies on specific cell positions, sheet names, or data formats that can change
• Untested — it works for the cases the builder thought of, but there’s no error handling for unexpected inputs
• Unversioned — there’s no history of changes, no ability to roll back, no way to compare the current version with what existed six months ago

What Happens When Macros Break

Macro failures range from annoying to catastrophic, and the worst ones don’t announce themselves.

The obvious failure: you click the button, you get an error message, the macro stops running. This is actually the best-case scenario because at least you know something’s wrong. You call the macro person, they fix it, life goes on.

The silent failure: the macro runs without errors but produces wrong output. Maybe a range shifted and it’s now pulling data from the wrong column. Maybe a new category in the data doesn’t match the hard-coded list in the VBA. The macro happily generates a report with incorrect numbers, and nobody notices until the numbers flow into a decision, an invoice, or a board report.

The cascading failure: one macro feeds into another. The first one breaks silently, producing slightly wrong output. The second macro processes that output, amplifying the error. By the time someone spots the problem, the damage has propagated through multiple files and multiple business processes.

The Security Risks Nobody Talks About

VBA macros are a well-known attack vector. Malicious macros can execute code on your computer, access your file system, send emails, and download files from the internet. This is why Microsoft has been progressively restricting macro execution — blocking macros by default in files downloaded from the internet and requiring explicit user action to enable them.

But the security risk isn’t just external. Your own macros can be a problem:

• Macros with hard-coded credentials — some macros connect to databases, email servers, or APIs using passwords typed directly into the VBA code. Anyone who opens the VBA editor can see them.
• Macros with file system access — VBA can read and write files anywhere on your computer or network. A poorly written macro could accidentally overwrite important files, or a malicious one could exfiltrate data.
• Macros that bypass business controls — if a macro auto-populates approval fields, generates documents with signatures, or sends emails on behalf of the user, it’s bypassing controls that exist for good reason.

Most businesses deal with this by either disabling macros entirely (breaking everything that depends on them) or enabling all macros with no restrictions (accepting the security risk). Neither is a good answer.

The “Just Rewrite the Macro” Trap

When a critical macro breaks and the original builder isn’t available, the temptation is to find someone who knows VBA and have them fix or rewrite it. This works in the short term but doesn’t solve the underlying problem. You’ve just moved the single point of failure from one person to another.

Worse, rewriting someone else’s undocumented VBA is genuinely difficult. Without knowing the original intent, every line of code is a puzzle. Does this IF statement handle an edge case that actually happens, or was it a workaround for a problem that no longer exists? Is this hard-coded value of 1.15 a GST calculation, a margin markup, or something else entirely? You can’t ask the code, and the person who could tell you is gone.

A Better Model for Business Automation

The logic in your macros is valuable. It represents real business knowledge — pricing rules, formatting standards, data transformations, workflow steps. The problem isn’t the logic. It’s the container.

Excel was never designed to be an application platform. VBA was a scripting layer bolted on to help with simple automation, not to build business-critical systems. When your macros cross the line from “nice shortcut” to “the business stops working without this,” they’ve outgrown their container.

Moving that logic into proper software means:

• Multiple people can maintain it — it’s written in a standard language, documented, and stored in version control
• It handles errors gracefully — unexpected inputs get caught and flagged, not silently ignored
• It’s testable — you can verify that the logic produces correct output before it runs against real data
• It’s secure — no more VBA with file system access running on every user’s machine
• It survives personnel changes — the logic exists independent of any one person’s knowledge

Practical Steps If You’re Stuck With Macros

If replacement isn’t on the cards yet, reduce your risk:

• Document what each macro does — not the code, the business outcome. “This macro takes the monthly sales CSV, applies the pricing tiers from the Pricing tab, and generates a customer price list PDF.” That’s enough for someone to rebuild it if they need to.
• List all macro files and their owners — know which files contain VBA, who built them, and what they do. Most businesses don’t even have this inventory.
• Add error handling — at minimum, wrap the main logic in an On Error block that shows a meaningful message instead of crashing silently. This turns silent failures into visible ones.
• Back up the files with macros — not just the data, the .xlsm files themselves. Version them manually if you must — save a dated copy monthly. If a macro breaks, you can at least roll back to last month’s version.
• Start identifying candidates for replacement — which macros are the most critical, the most fragile, and the most dependent on one person? Those are your priorities if you ever do move to proper software.

Excel macros are a perfectly reasonable way to start automating repetitive tasks. They become a liability when the business depends on them but can’t maintain them. Recognising that moment — and acting on it before the macro person’s departure forces the issue — is the difference between a planned transition and an emergency scramble.