Many Excel files are simple lists. Some are more: they have buttons, input forms and routines that run at a click. The costing pulls prices from another file, the quote comes out as a finished document, the weekly schedule assigns jobs to staff, the report goes to the managing director by email every Monday. Behind all this are macros, written in VBA, the programming language built into Microsoft Office.

Workbooks like these are applications, even if nobody calls them that. They have often grown over years, are surprisingly capable and fit the business exactly. That is precisely why it is risky when nobody knows any more how they work.

When an Excel list in general reaches its limits is covered in our guide Excel as a database. This article is about the next step: workbooks with program code inside them.

Where macros live in the business

  • Costings: materials, working time, surcharges and price breaks, and a price at the end.
  • Quotes and order confirmations: pick the items, press a button, get a finished document.
  • Planning: shifts, field jobs, production sequence, machine allocation.
  • Reports: read in data from the ERP, prepare it, send it out as a report.
  • Small interfaces: read in a file from the online shop or a supplier and reshape it for another program.

Signs that it is getting risky

  • One person knows the code. They wrote it, and only they dare to change anything. If they are ill or leave the business, every change becomes a gamble.
  • Macros get blocked. Microsoft has tightened macro security in recent years. Files that come from the internet or by email often open in newer versions of Office with macros blocked. If you send the workbook to customers or your field staff, you will notice this first.
  • Updates cause trouble. A macro only runs in the 32-bit version of Office, a reference to an old add-in is missing after the upgrade, a function behaves differently in the new version.
  • Copies travel around. Everyone has their own version of the file, sent by email, saved on a laptop. Nobody knows for sure which one has the current price list.
  • There are no permissions and no log. Anyone who can open the file can also change the code. Who changed which figure and when cannot be traced.
  • Nothing works on the move. Macros generally do not run on a phone or in the browser.

None of these signs on its own is a reason to replace the workbook. If several apply, it is worth taking a closer look. More signs are described in our guide Signs your system is at its end.

What is inside the workbook

Before anything is replaced, write down what the file does today. These parts deserve attention:

  • Formulas: Where are calculations made, and by which rules? The most important business rules often sit in a long formula that nobody looks at any more.
  • Macros: What happens when each button is pressed? Which macros run automatically when the file is opened or saved?
  • Hidden sheets and columns: This is often where price tables, intermediate calculations and lookup lists live.
  • Links to other files: Does the workbook pull data from a second file, a database or the network drive? These connections are the first to break.
  • Outputs: Which documents, emails or files does the workbook produce, and who needs them?
  • The people: Who works with it, how often, and for what exactly?

The review should be done with the person who wrote the code, while they can still be reached. The code is read, not guessed at. It often turns out that part of it has not been needed for a long time and another part is indispensable every day.

Three routes

Tidy up and keep it in Excel

If only a few people use the workbook and it works well, putting it in order is often enough: one central version in a fixed place, the code commented and backed up, a short description of what each button does, and a second person who knows their way around it. That costs little and removes the biggest risk.

Data into a database, Excel stays the front end

Often the real problem is not the calculating but the data: price lists, customers, orders, spread across many copies. The data can then move into a database first, which everyone accesses. Excel remains the tool for costings and reports but reads from one shared source. Little changes for staff, and the copies disappear.

Move the workflow into an application of its own

If several people work in the same workflow, if permissions and a log are needed, or if the workflow should also work on the move, an application of its own is the more honest route. The workbook is the best template for it: its sheets show the areas, its formulas the rules, its buttons the working steps.

Step by step rather than all at once

Switching off a workbook that has grown over years on a fixed date is risky, because only in daily use does it show which rule was forgotten. It is usually safer to move one part at a time: first the area that causes the most trouble, such as the price list that exists in ten copies. The workbook keeps running until its last part has been replaced. Both routes are compared in our guide Cut-over date or step by step.

We replace old systems with ElbDesk, the foundation we build on: ElbDesk brings sign-in, permissions, collaboration and documents, and only what makes your process yours is developed. What that looks like step by step is shown on our page on replacing old systems. A similar approach for databases is described in our guide Replacing an Access database.

Checklist before you start

  • Which workbooks with macros are in use in the business, and who uses them?
  • Who wrote the code, and can that person be reached for the review?
  • Is there a central, backed-up version of each workbook?
  • Which macros run automatically when a file is opened or saved?
  • Which other files or programs does the workbook read from or write to?
  • Which part causes the most trouble today?