A VBA-enabled workbook can appear reliable until a small change exposes an assumption nobody recorded. A renamed worksheet, moved folder, altered import file, changed macro setting or copied workbook can interrupt a process that staff use every day.
For UK SMEs, the main risk is rarely VBA alone. It is the combination of macros, formulas, manual steps, shared files and business knowledge held by a small number of people. When that workbook supports approvals, quotations, stock, jobs, production, inspections or reporting, it needs to be maintained as an operational process rather than treated as an ordinary file.
Practical rule: If a VBA workbook controls a repeatable business action, treat changes to it as operational changes.
Why VBA spreadsheet maintenance becomes difficult
VBA often accumulates gradually. A workbook may begin as a useful model, then gain data-entry sheets, buttons, imports, generated documents and exception handling as the business grows. Each addition can be sensible in isolation, but the combined process may become hard to understand or test.
The person who created the macro may know that a button must be run from a particular sheet, after a particular import and before a report is issued. That knowledge may never appear in the workbook itself. When they are unavailable, a routine change can become risky because nobody can confidently identify what else depends on it.
Common sources of maintenance risk include:
- VBA procedures that refer to specific worksheet names, ranges, folders or file names.
- Hidden sheets, named ranges and external links that contain part of the process logic.
- Manual checks that users perform because the workbook does not guide them through the workflow.
- Multiple copies shared through email, downloads or synchronised folders.
- Changes made directly in the live workbook without a separate test copy.
- Macros that behave differently because users have different Excel versions, devices or security settings.
- Key-person knowledge about exceptions, recovery steps and the intended order of actions.
A macro can still run while producing an incomplete or misleading output. That is why successful execution is not enough evidence that a maintenance change is safe.
The maintenance failures to watch for
| Failure mode | What staff may see | Underlying maintenance concern |
|---|---|---|
| Broken references | A button fails after a tab, folder or source file changes | VBA depends on exact names, paths or workbook structure |
| Incorrect output | A report runs but omits, duplicates or misclassifies information | The logic has changed without an adequate test of the result |
| Version sprawl | Staff ask which file is the current version | Different copies contain different data, formulas or VBA code |
| Informal fixes | A colleague edits formulas or code to solve an urgent issue | Production changes are being made without review or rollback |
| Security prompts or blocks | A macro works for one user but not another | Excel configuration and organisational security controls vary |
| Slow or unstable workbooks | Users bypass checks, force-close Excel or create temporary copies | Technical overhead is encouraging workarounds outside the process |
| Unclear ownership | Nobody can approve a change with confidence | Technical knowledge and process responsibility are not defined |
These are governance problems as well as technical problems. The business needs to know what the workbook does, who can change it, how a change is tested and which version is approved for live use.
VBA dependencies that are easy to miss
A workbook may contain dependencies in places that everyday users never see. Before editing code or formulas, identify the wider set of components involved in the workflow.
Workbook structure
Look beyond the visible input sheet. Record hidden worksheets, named ranges, tables, formulas, pivot tables, charts and workbook links. A visible change can affect logic held elsewhere in the file.
VBA entry points
List every way code can run, including buttons, workbook events, worksheet events, user forms and procedures triggered by another macro. A process may fail because code runs automatically at the wrong point, or because a user no longer knows which action starts it.
Files and folders
Check imports, exports, templates, generated documents and network or cloud locations. A path embedded in VBA can become fragile when files are moved, renamed or accessed from a different device.
Manual process steps
Ask staff what they do before and after pressing a button. They may clean data, check statuses, save copies, send emails or correct exceptions manually. Those steps are part of the real operating process, whether or not VBA captures them.
People and permissions
Identify who enters data, changes formulas, edits code, approves exceptions and distributes outputs. If every user can alter the same workbook structure, accidental changes are difficult to distinguish from authorised maintenance.
Testing VBA changes without disrupting live work
A business-critical workbook needs more than a quick check that its main button still works. Testing should confirm that the process produces the right result across normal and exceptional situations.
A practical maintenance routine can include the following:
- Keep an approved live version. Make clear which workbook is in operational use and prevent informal copies from becoming alternatives.
- Use a separate test copy. Do not make unproven changes in the workbook that staff are using for live work.
- Define representative test cases. Include normal inputs, known exceptions, blank fields, changed source files and approval scenarios that matter to the business.
- Check outputs, not just errors. Compare reports, documents, status changes and downstream data with the expected business outcome.
- Record the change. Note what changed, why it changed, who approved it and how it was tested.
- Plan recovery. Retain the prior approved version so the team can return to it if a release causes an issue.
This does not require a large software team. It requires a proportionate level of control for the importance of the process. A workbook that produces occasional internal analysis may need less formality than one used to approve customer terms or issue operational documents.
Version control is more than file naming
Naming files clearly can help, but version control is not solved by adding words such as "final" or "latest" to a workbook name. The key question is whether everyone uses the same approved process version.
Useful controls include:
- One clear location for the authorised live workbook.
- Restricted access to formulas, code and workbook structure where appropriate.
- A documented owner for change requests and releases.
- A change record that explains the purpose and scope of each update.
- A test copy that is separate from live operational data.
- Clear instructions for users when a new version is released.
For shared-workbook concerns, see how multiple users editing Excel can create process risk. The core issue is not simply simultaneous editing. It is whether people are working from the same rules, data and approved version.
Platform and macro-security dependencies
VBA workbooks do not operate independently of their environment. The Excel application, operating system, storage location and security policy can all affect whether a macro is available or behaves as expected.
Before assuming that a macro fault is caused by the code, check:
- Which Excel environment the workbook is expected to run in.
- Whether users access a controlled file or synchronised local copies.
- Whether macro settings differ between users or devices.
- Whether the file location affects macro behaviour.
- Whether organisational security controls have changed.
- Whether a source export, template or linked workbook has changed structure.
Macro security should be governed with IT or the person responsible for business systems. A maintenance plan that relies on users changing security settings individually is unlikely to provide consistent operational control.
Is the workbook still a model, or has it become a business process?
Excel remains useful for modelling, forecasting, flexible calculations and exploratory analysis. Those uses benefit from the ability to adapt a workbook quickly.
The maintenance challenge changes when a workbook becomes the place where work is coordinated. It may then hold records, direct approvals, create quotations, schedule production, track inspections or produce recurring reports. The workbook is no longer only analysing information. It is helping to run the process.
Use these questions to assess the difference:
| Question | What a concerning answer can indicate |
|---|---|
| Does the workbook trigger or record a business decision? | Process logic is embedded in a file rather than managed as a controlled workflow |
| Do several roles depend on it? | Access, handoffs and accountability need clearer control |
| Are there regular exceptions handled by experienced staff? | Important rules may be undocumented |
| Can a new user operate it safely from written guidance? | The process may depend on personal knowledge |
| Can a change be tested away from live work? | Routine maintenance may be creating production risk |
| Would a wrong output affect a customer, supplier, payment, job or compliance record? | The workbook has operational consequences beyond analysis |
A small workbook or a single user does not automatically make the process safe. What matters is the consequence of an incorrect change and the ability to maintain the workflow reliably.
When continued VBA maintenance is reasonable
Continued maintenance can be appropriate where the workbook remains a flexible model, the owner understands its logic and changes can be tested with confidence. In that situation, the priority is to document dependencies, establish a controlled version and make change ownership clear.
A managed custom application becomes worth considering when the workbook has become a repeatable, role-based operational workflow and dependable maintenance is increasingly impractical. This is not a reason to discard useful spreadsheet modelling. It is a way to separate flexible analysis from the controlled process that staff need to follow repeatedly.
For context on the wider decision to move an operational spreadsheet into an application, see our guide to turning Excel into a web app. That decision should follow a clear understanding of the workbook's current dependencies, maintenance burden and governance needs.
A practical next step
Start by documenting the live workbook before making another urgent fix. Record its users, entry points, source files, outputs, manual steps, known exceptions and the environments in which it is used. Then decide whether the business can give that process the ownership, testing and release control it needs.
If the workbook is central to daily operations and the maintenance burden is becoming difficult to manage, a Free Fit Check can help you discuss whether a managed custom application is appropriate for the workflow.