VBA

The Reality

VBA macros are the invisible infrastructure of thousands of businesses. They sit inside Excel workbooks and Access databases, automating reporting, transforming data, generating invoices, calculating pricing, running allocations, and performing dozens of other tasks that would otherwise require hours of manual work. They were usually built by the most technically capable person in a department: not a developer, but someone who understood the business problem and learned enough VBA to solve it.

These macros work. Many have been running reliably for years. The issue is that they are fragile in ways that are not obvious until something goes wrong. They live inside files that get emailed around, copied to desktops, and saved in personal OneDrive folders. They depend on specific worksheet layouts: add a column and the macro breaks. They have no version control, no tests, no error handling beyond what the original author thought to add. And the person who wrote them may have moved on without documenting what they do or why.

The Risks of Doing Nothing

• Person dependency. When the person who built the macro leaves, nobody else can fix it. The business process it supports becomes a black box.
• Office update breakage. Microsoft regularly updates Office, and VBA behaviour changes between versions. A macro that works in Office 2016 may behave differently in Microsoft 365.
• Data integrity risk. VBA macros operating on spreadsheets have no transaction safety. A macro that fails halfway through a data transformation can leave your data in an inconsistent state with no rollback.
• Audit and compliance gaps. If business-critical calculations happen inside a macro, auditors will ask how you validate them. “Dave wrote it and it seems right” is not an answer that satisfies a compliance review.

How We Approach This

The first step is always discovery. We need to understand what the VBA actually does, not just what people say it does. We read the code, trace the data flows, identify the inputs and outputs, and document the business rules embedded in the macros. This documentation alone is valuable: it turns tribal knowledge into something the organisation owns.

From there, the path depends on complexity. Simple macros that transform data or generate reports can often be replaced with lightweight web tools or automated workflows that do the same job with proper error handling, logging, and version control. For complex business logic (pricing engines, allocation algorithms, financial models), we extract the rules into a proper application with tests that verify the new system produces identical outputs to the old macros.

We always run the old and new systems in parallel during transition. The macro keeps running while the replacement proves itself against real data. Nobody is asked to trust a new system on faith. The approach mirrors what we do across legacy systems more broadly: structured, evidence-led, with no big-bang cutover.

What You End Up With

• Business logic extracted from spreadsheets into a maintainable, testable system
• Proper error handling and logging replacing silent failures
• Version control and audit trails for business-critical calculations
• Elimination of person dependency: the logic is documented and owned by the organisation
• Data integrity protections that spreadsheet macros cannot provide

For businesses where the spreadsheet itself is the problem rather than just the macros inside it, see replacing spreadsheet-led operations.

What We Have Seen

We helped a professional services firm that relied on an Excel workbook with over 4,000 lines of VBA to calculate monthly billing across forty client accounts. The workbook had been maintained by one person for eight years. When that person announced their retirement, the firm had six months to act. We documented the business rules, rebuilt the billing logic as a web application with full test coverage against three years of historical billing data, and ran both systems in parallel for two months. Every output matched. The firm now has a billing system that any authorised person can run, with a complete audit trail and no single point of failure.

How Much Time Have You Actually Got?

VBA is unusual: the language is not going anywhere, but the conditions it runs under keep tightening, and it hides in places nobody inventories.

You have years, not months, if: the macros live in a handful of known files, on a shared drive, and two or more people understand them.

You are inside two years if: your organisation is moving to 64-bit Office. Old Declare statements calling Windows APIs need PtrSafe and correct pointer types to survive the jump; workbooks that have run for a decade stop compiling on the new machines. This migration catches people out constantly because the failure appears on the desktop rollout, not in IT’s project plan.

You are inside twelve months if: macro-enabled files arrive from outside the organisation: customers, suppliers, portals. Microsoft now blocks VBA macros in files downloaded from the internet by default. Anything in your process that depends on someone opening an emailed macro workbook is already failing intermittently, and the failure looks like user error rather than a policy change.

You have a hard problem, not a deadline, if: nobody can produce a list of which files contain macros. Unknown scope is the real risk with VBA. You cannot plan around code you cannot find.

What Breaks First

1. Trust boundaries. Macro blocking, protected view, and security policy stopping files that used to work.
2. The 64-bit transition, which breaks API declarations en masse.
3. The file itself. VBA lives inside a workbook or document. One person saving over it on a shared drive erases the code, and there is no version history.
4. Understanding, because VBA is written by the person closest to the problem, who is rarely a developer and rarely documents it.
5. Excel or Office, essentially never. VBA remains supported.

Check These Six Things This Week

• Where are the macro-enabled files? Search your shared drives for .xlsm, .xlsb, .docm, .accdb. The result is usually larger than expected.
• For each one: who wrote it, and do they still work here?
• Is the organisation on 32-bit or 64-bit Office, and is a migration planned?
• Does any process rely on a macro file arriving by email or download? Those are already fragile.
• Is any of it in version control, or backed up in a way that keeps history? A file that overwrites itself is one careless save from gone.
• Does any macro touch money, payroll or regulatory reporting? Those get attention first regardless of the technical picture.

When You Genuinely Don’t Need To Act

A well-understood macro, in a known file, that saves someone an hour a week, is one of the highest-return pieces of software in most businesses. It cost nothing and it works. There is no virtue in replacing it with a “proper system” that costs thousands and does the same job.

Act on the ones that are load-bearing, unowned or invisible. Leave the rest alone, genuinely.

Let Us Help Untangle Your VBA

It is easy to underestimate VBA macros because they live inside familiar tools. But the business logic inside them is often critical, and the risk of losing it is real. Whether you need to document what you have, reduce person dependency, or migrate to something more robust, we can help you do it safely. The risk-managed approach is covered in more detail on the risk reduction in legacy transformation page. For systems that have passed the point of patches and need full support, see legacy software support. Get in touch.