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 critically, 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.
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
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.
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. Get in touch.
