Why I Killed My 240KB Excel Macro and Built a Full-Stack MES Instead

There's a certain archaeology that happens when you inherit a manufacturing codebase. You start pulling threads — why does this report take four minutes to generate? — and you end up excavating something that should have been decommissioned years ago. In our case, it was a 240KB Excel macro. Not a small macro. A civilization built inside a spreadsheet.

That macro summarized Bills of Materials (BOMs), cross-referenced component libraries, flagged substitutions, and generated production reports. It worked, in the way that load-bearing duct tape works. Nobody fully understood it. Nobody wanted to touch it. And the moment the SMT line scaled — more boards, more component variants, tighter tolerances — it started cracking under the weight.

This is the story of what I replaced it with, and why the migration was harder than anyone expected.

The Real Cost of VBA Technical Debt

The 240KB number isn't just trivia — it's diagnostic. A healthy Excel macro is tight, purpose-built, maybe 10–20KB. When it bloats to 240KB, it means decades of patches: functions calling functions calling functions, global variables that nobody dares rename, and a complete absence of version control. Git doesn't track .xlsm files the way it tracks .py files. Every change is a leap of faith.

VBA's deeper problem isn't the language itself — it's the execution environment. A macro lives inside Excel, which means it's subject to whatever is happening on that machine at that moment: screen resolution quirks, installed Office version, shared network drives timing out. These aren't edge cases in a factory environment. They're Monday morning.

The SMT manufacturing context makes this even more fraught. A BOM summary tool doesn't just read static data — it needs to query live APIs, connect to machine interfaces, and handle hundreds of thousands of component records. VBA has no meaningful concurrency model. Every API call blocks. Every row of data processed is one more tick of the UI freezing. For a 10-row spreadsheet this is fine. For 50,000 components, it's a warranty violation.

What the New Architecture Actually Needs

The replacement wasn't just "write the same thing in Python." The production environment had grown to demand something structurally different:

External API connectivity. Our component library needed to sync against the Mouser Electronics REST API — real-time pricing, availability, and parametric data. This is trivially handled in Python's requests or httpx, but genuinely awkward in VBA, which treats HTTP calls as COM object rituals.

Direct machine interface. The SMT placement machines expose data through CGI endpoints — a thin HTTP layer over machine telemetry. Reading live machine state, error logs, and production counters meant building a proper HTTP client with retry logic, timeouts, and structured error handling. VBA could theoretically do this. Python does it in 15 lines.

Local data persistence. Not a cloud database, not a shared network drive — a local SQLite database on the edge machine, capable of storing millions of component records and querying them in under 50ms. This distinction matters: in a factory, network connectivity is unreliable by design. The system needed to work offline, period.

A real UI layer. Production engineers are not developers. The interface needed to be fast, visually clear, and respond to their actual workflow. Not a grey Excel dialog. Something modern.

Building the Bridge

The migration happened in three phases, and the first two were painful in instructive ways.

Phase one was direct translation: convert the VBA logic to Python line by line. This felt productive and was almost entirely wrong. VBA macros don't translate — they're written for the Excel object model, not for general computation. What looked like a BOM parser was actually 60% Excel worksheet manipulation dressed up as logic. Stripping the Excel scaffolding out revealed how little actual domain logic existed. We basically had to write the thing from scratch.

Phase two was the API integration. Connecting to the Mouser REST API sounds straightforward, and the API itself is well-documented. What wasn't documented was how chaotic the component data is in the real world. A single 10kΩ resistor might appear in eight different formats across different BOM files: "10k Res", "Resistor 10 kilohm", "RES 10K 1%", "10000 Ohm SMD". The Mouser API returns clean canonical data. Your customers send you anything but canonical data. The normalization layer between them became one of the most complex pieces of the system — more on that in a future post.

Phase three was the actual full-stack integration: SQLite backend, Python business logic, and a JavaScript/CSS frontend rendered through a local server. The Eel framework handled the Python-to-JavaScript bridge. This is where the architecture clicked. For the first time, every component in the system had a clear responsibility, a testable surface area, and a commit history.

What "Full-Stack" Actually Means in a Factory

The term gets overloaded. In a web context, full-stack means frontend-plus-backend. In an edge manufacturing context, it means something closer to: a self-contained, locally-deployed application that bridges hardware machines and human operators, with its own data layer, business logic, and UI — and that works even when the internet doesn't.

That reframing matters for architecture decisions. You don't reach for Django or FastAPI. You don't provision a cloud database. You pick tools that are light, portable, and deterministic. Python, SQLite, and a browser-mode Chrome window running local JavaScript are a surprisingly powerful stack for this problem domain.

The VBA macro, for all its faults, had one genuine virtue: it worked on anyone's laptop with Excel installed. The full-stack replacement needed to match that operational simplicity. A single executable, a local database file, no server to manage. That constraint shaped every architectural choice downstream.

The Lesson

The 240KB macro didn't fail because Excel is bad or VBA is inadequate for small scripts. It failed because the problem outgrew the tool, and the tool was never designed to evolve. Good software architecture isn't about choosing the best technology in the abstract — it's about choosing technology that can grow with the problem.

The full-stack Python replacement isn't perfect either. But it can be tested, versioned, debugged, extended, and understood by someone who joined the team last month. In manufacturing, that's not just a quality-of-life improvement. It's the difference between a system you trust and one you pray at.

The assembly hub I've been building is the direct descendant of that decision. If you want to dig into the specific modules — the BOM normalizer, the machine interface layer, the SQLite schema — those are coming in subsequent posts.