I wrote some VBA to try to reduce the size of the workbooks (dozens of MB) but originally it was just massive nested lookups and conditionals written by the people who had been there before me.
The problem with VBA or PS is that you'll be posted out in 2-3 years and your successor might not even know what VBA is or how to access it much less how to write it.
I ended up writing a lot of documentation based on my XLSX archaeology.
Nice, that makes sense. I'm always curious when I hear about other giant Excel monoliths still in use if they're truly a huge tangled web of formulas, or more like a VB6 program stapled to some worksheets that can be distributed as a single .xlsm file like ours.
You're definitely right that 99% of Excel users have no idea that an entire IDE is lurking in the background with Alt-F11 and wouldn't even know where to begin to do a simple fix/tweak. Whereas even a complex formula can at least be mentally parsed as a math equation with some strange ALLCAPS() names scattered throughout, or copy/pasted into Google/an LLM to get started, so much more discoverable than VBA.
Not to mention that the VBA IDE is a horrible dev experience in pretty much every way and frozen in time while regular Excel is actively maintained.
I've explored pulling out all the critical business logic currently in VBA and having Claude Code rewrite to VB.NET that can be called from Excel to gradually phase out VBA for maintainability. But unfortunately despite VBA essentially driving everything, it's tightly coupled to the Excel spreadsheet data model. So I'd have to use a .NET library to emulate the Excel worksheet functions, or pretty much do a full rewrite to use modern .NET methods for the in-memory data manipulation stuff.
Without good visibility into the Excel side of the VBA, Claude Code/Gemini CLI were just flailing so I abandoned that idea for now until I can find a solid conversion path.