Excel and VBA systems that run part of the business
We take spreadsheets that have grown into business systems and make them dependable: tidied and documented, backed by a proper database, or replaced by a web application.
What you probably want to know first
- Can you support our existing application?
- Yes, provided you hold the workbook, which contains the macros and formulas that are its source code. Support is harder where it links to other workbooks, where macros rely on API declarations or ActiveX controls that break on 64-bit Office, or where macros refer to fixed cell positions and only one person knows what they do.
- What happens in the first assessment?
- Our fixed-price code audit, one to two weeks: a short call, then read access to a copy of the workbook and, where possible, the files it links to and any database behind it. We trace the macros and formula chains, run automated analysis and read by hand the parts that matter. You get a written report and a walk-through call.
- What access do you need?
- Read access to a copy of the workbook and, where possible, read-only access to the files it links to and to any database or server behind it. Nothing changes on the live file: we work from a copy and nobody stops using the original. A confidentiality agreement can be signed before anything is shared.
- What sets the cost and the timescale?
- For the audit: the size of the workbook and the number of separate files, whether we can see the linked files and any database as well as the workbook, and unusual or mixed technology. A replacement is sized by the number of sheets and how complicated the formulas and macros are, and the audit fee is credited against it.
- What have you done with systems like ours?
- We have worked mostly on the Microsoft stack since 2012, and most of the systems we maintain were built by someone else. Turning a business-critical Excel workbook into a web application, with the calculations checked against the spreadsheet's own results, is one of our fixed-price packages. Our case studies are on the site.
When a spreadsheet becomes a system
Excel is flexible enough that almost any process can be started in it. Quotes, rotas, stock counts, price lists, commission calculations and month-end packs often begin as a workbook someone built to save time. Formulas are added, then a few recorded macros, then VBA written by whoever was willing to learn it. Years later the workbook has buttons, hidden sheets and links to other files, and part of the business cannot run without it.
Nothing about that is foolish. It was the quickest way to get a working tool. The difficulty is that Excel is at its best with one person analysing figures, and a shared business process asks for things it was never meant to provide.
What tends to go wrong
- Version confusion. Copies are saved with dates and initials in the file name and emailed around. Two people update different copies and nobody can say which holds the right figures.
- One person understands it. The macros were written by one employee, are not documented and have never been read by anyone else.
- No access control. Anyone with the file can see and change everything in it. Sheet protection guards against accidents and is not a security measure.
- Office updates break it. A move to 64-bit Office breaks some API declarations and older ActiveX controls. Office now blocks macros in files that arrive by email or from the internet. A missing reference on one PC stops the code on that PC only.
- Macros tied to cell positions. Recorded macros refer to fixed cells and sheet names. Insert a column and the macro carries on without complaint, working on the wrong data.
- Silent errors. A formula overwritten with a typed number, or a range that stops one row short, produces a plausible wrong answer with no warning.
- One user at a time. Macro-heavy workbooks rarely cope with several people entering data together, and VBA does not run in Excel for the web.
Your options
Tidy and document. For a workbook that is basically sound. Macros are rewritten to use named ranges and tables instead of fixed positions, error handling is added, inputs are separated from calculations, and the whole thing is written up so that a second person can maintain it.
Move the data into a database and keep Excel for reporting. The records go into SQL Server, entered through a simple screen, and Excel connects to the database to produce the pivot tables and reports people already use. There is one copy of the data, and Excel goes back to being an analysis tool.
Replace it with a web application. When several people need to enter data, approvals are involved, or customers and suppliers need access, a web application provides logins, permissions, validation and a history of changes. Our spreadsheet to web application package covers this at a fixed price.
How we approach it
We start with the people, then the file. The users show us what they do with the workbook, including the steps that happen outside it: the export pasted in every Monday, the column someone corrects by hand. Then we map the workbook itself, tracing each macro and formula chain back to its inputs.
Whatever replaces the workbook is checked against it. Old and new are run side by side on the same real inputs until the answers agree, and each difference is explained.
We also say so when the answer is to leave it alone. A spreadsheet used by one person for analysis is Excel doing its job. The fuller case for and against moving is under replacing spreadsheets. Where the workbook has already been joined by an Access database, see Microsoft Access, and where nothing off the shelf fits the process, see bespoke software.
Talk to us
Describe the system and what you need. You will hear back from someone who can answer technical questions.
Discuss your Excel workbook 0800 433 7990What we do with Excel and VBA systems
- Document what it does
- We trace every macro, formula chain, external link and input, so the process is written down and no longer lives in one person's head.
- Tidy and stabilise
- Recorded macros tied to fixed cell positions are rewritten, error handling is added and dead sheets are removed.
- Make it survive Office updates
- Code is corrected for 64-bit Office and current macro security, and signed so that it runs without warnings being clicked through.
- Move the data to a database
- Records go into SQL Server, and Excel connects to it for analysis and reports.
- Replace with a web application
- Data entry, calculation and approval move to a web application with logins, permissions and a history of changes.
- Keep Excel for what it does well
- Reports and exports still open in Excel, fed from one agreed set of data.
How we replace or repair a workbook
Walk through the process
The people who use the workbook show us what they do with it, step by step, including the manual parts.
Map the workbook
We list the sheets, macros, formulas, links to other files and every place where data is typed or pasted in.
Decide what it should become
Tidy it, put a database behind it or replace it, depending on how many people use it and what an error would cost.
Build and check against the original
The new version is run beside the workbook on real figures until the results agree.
Switch over and retire the old file
Users move to the new version and the old workbook is archived as read-only.
A good fit when
- A process such as quoting, scheduling, stock control or month-end depends on one workbook.
- Only one person understands the macros, and they are leaving or overloaded.
- Copies of the file are emailed around and nobody is sure which is current.
- Macros stopped working after an Office or Windows update.
- Several people need to enter data at the same time.
Probably not for you if
- The spreadsheet is one person's analysis tool; that is what Excel is for.
- The process is still changing every few weeks; a spreadsheet may be the right tool until it settles.
Questions we are asked
Is there anything wrong with running a process on Excel?
Not for analysis, or for one person's working file. The trouble starts when a workbook becomes the place a shared process lives: several people entering data, figures that others depend on, and logic in macros nobody has reviewed. Excel keeps no dependable record of who changed what, and a formula can be replaced by a typed number without anyone noticing.
Why have our macros stopped working?
Common causes are a move from 32-bit to 64-bit Office, which breaks some API declarations and older ActiveX controls; Office blocking macros in files that came by email or from the internet; a missing reference on one PC; and a sheet or column being moved when the code points at fixed positions. Most are quick to diagnose.
Can we keep using Excel after the data moves to a database?
Yes. Excel connects well to SQL Server, so people can keep their pivot tables, charts and reports. The difference is that the data is entered and stored in one place, and Excel reads it instead of holding the only copy.
How do you make sure the replacement gives the same answers?
By running old and new side by side on the same real inputs and comparing the outputs. Each difference is investigated. Sometimes the comparison finds an error in the original workbook, and you decide which behaviour is correct.
How long does it take?
Tidying and documenting a workbook is usually a matter of days to a few weeks. A database with Excel on top, or a web application, depends on how many processes the workbook supports. A replacement for a single well-understood process often suits a fixed price.
Tell us about your system
Say what it does, what it is built on and what is worrying you. We will reply with what we would look at first and whether we are the right people to help.