Inventory systems in Excel with VBA: the honest limits

The key facts

• A VBA inventory workbook is a genuine single-user system: forms, validation, macros for movements — impressive and often sufficient at small scale.
• The ceiling is structural: one file means one truth, and Excel cannot host two users' simultaneous, auditable writes.
• The migration trigger: the second location, the second user, or the first quantity nobody can explain.
• The migration path is kind: spreadsheet import is a first-class feature in real systems — your columns, your product list, into a record with guardrails.
• The full picture: how the inventory record works is the hub page for everything above.

What a VBA system genuinely handles

Give the VBA builder their due: a well-built workbook — a products sheet, a movements sheet, UserForms for receipt and issue, data validation, macros that write movements rather than overwrite totals — is a real application. It beats the unstructured spreadsheet most businesses start with, and it teaches exactly the right lesson: record movements, derive quantities. If the builder in your business did that, they independently arrived at the same architecture production systems use (the Java-builder angle names it formally).

Where it works, it works because the volume is one user's attention: a shop counter, a single store, a stock list reviewed weekly. Many businesses run this way for years, and there is no dishonest urgency here — if nobody else touches the file and volumes are slow, it is fine.

Where the ceiling is — exactly

Concurrency. Excel cannot host two users writing the same sheet at once. Shared workbooks and OneDrive co-authoring are workarounds with real failure modes — the second writer's macro either errors or, worse, the write happens and the movement history forks.

Integrity. A workbook has no guardrails: no way to stop an issue below zero, no audit trail beyond "the cell changed", and one accidental sort-overwrite from a bad day. Data validation is a suggestion, not a gate.

Reach. The warehouse floor does not have the workbook open — scanning a barcode means a laptop and a macro, not a phone or tablet at the shelf. And quantities do not reach a website, a portal or the bookkeeper without someone emailing the file.

Each of these alone is tolerable; together they define the moment the business needs a record rather than a file. The Excel-only version of this problem and the spreadsheet-to-cloud pattern cover the earlier stages.

Migrating without losing your work

The good news: a disciplined VBA system is the best possible preparation for a real inventory system, because your columns are already a data model. The migration is: export products and current quantities (or better, the movements history), import into the system, run a stocktake to establish truth, and record movements there from day one. BSimple — built by the people writing this, so weigh that — treats spreadsheet import as a first-class feature: products in, live quantities across locations, guardrails on bad entries, and barcode scanning when the floor is ready. Accounting stays with Xero or MYOB; US integrations are rolling out.

Run the trial with your actual workbook open beside it — the import is an afternoon, and the honest comparison is your macro doing less and less.