Reading 32,000 lines of undocumented VBA, straight from the file
A housing association's Excel and VBA application, 113 modules read in full, then an executive review and a fixed-price plan to replace it with a supported web application.
Spreadsheets into systems
When the spreadsheet has become the system
Macros, links between files, copies of copies, and one person who understands it. We read the workbook end to end, rebuild it as a proper system, and move its data across, reconciled.
- 01The workbookExcel, VBA or Access
- 02Read end to endFormulas, macros and links
- 03The systemDatabase, screens, audit trail
- 04Your dataMoved and reconciled
Signs it's time
- Copies of copies, and nobody sure which one is current.
- One person understands it, and things wait when they are away.
- Errors found after the figures have gone out.
- No record of who changed what, or when.
- It is slow to open, or breaks when two people use it at once.
- The same figures typed in again somewhere else.
Before and after
The workbook
- 1J-1042's total was typed over its formula: 15 hours at 48.00 is 720.00, not 620.00.
- 2“Sen” isn't on the rate list. With no exact-match flag, the lookup quietly takes the Lead rate.
- 3J-1044's hours are stored as text, so the hours total reads 41, not 51.
- 4J-1045 shows #REF!: the column its rate pointed at was deleted.
- 5The total stops at row 5, so J-1045 is left out of it.
The system
- 1Totals are worked out from hours and rate, and can't be typed over.
- 2Grades come from a list and rates from one table, matched exactly.
- 3Hours is a number field, checked as it's entered.
- 4Rates are looked up by grade, not by column position.
- 5Every job is in the total, and the move is reconciled against the workbook.
How it runs
- 01
Read it end to end
Formulas, macros, hidden sheets and links to other files come straight out of the file, so the review covers what is out of sight as well as what is on screen. That is how we read 113 VBA modules for a housing association. Read the write-up
- 02
Decide what stays
Some of it is business rules worth keeping exactly. Some is a workaround for something Excel couldn't do. The review says which is which, and what replacing it would cost.
- 03
Build the system
A database with the rules written down and tested, screens for the jobs people actually do, permissions, and a record of each change: who, when and what it was before.
- 04
Move the data
History comes across with the same checks as any migration: counts and totals from the workbook against the new system, and anything not moved listed with its reason. How a migration runs
- 05
Switch over
The workbook runs alongside until the answers match, then becomes read-only. Anyone who still wants a spreadsheet can export one.
What you get
- A written review of the workbook: what it does, what is fragile, and what is worth keeping.
- A fixed price for the replacement, based on that review.
- The new system, documented, with its data moved and reconciled.
- A handover, so your team can run it without us.
What we build with
.NET and React for the application, SQL Server or Azure SQL underneath, Python where it suits the job, and Power BI for reporting. Sometimes the honest answer is a smaller change: the same spreadsheet, fed from one database instead of a dozen copies. The review says which.
From the write-ups
Questions people ask
- Will we have to stop using Excel?
- No. The new system exports to Excel, and some analysis is best left there. What changes is where the data lives and who can change it.
- Can you replace an Access database?
- Yes. Access is often where a spreadsheet went next, and the same approach applies: read the tables, queries, forms and VBA, then rebuild.
- Could you just fix the spreadsheet?
- Sometimes that is the right answer, and the review will say so. If the trouble is in a handful of formulas, fixing them costs far less than replacing anything.
- How long does it take?
- The review comes first, and it ends with a fixed price and a timeline for the replacement. Before the review, any estimate would be a guess.
- Do you need the file straight away?
- No. Start with a description. The file comes later, under an NDA, and we work on a copy. How access works
Describe your spreadsheet
Tell us what it does, who uses it and what goes wrong with it. No files yet: those come later, under an NDA.