Any VBA? I help out with an extremely complex 15+ yr old Excel workbook designed for managing school debate tournaments that's still in active use in the PNW debate world but the complexity is 90% from VBA.

It does inject Excel formulas when creating worksheets but also heavily uses UI controls/dialogs made with the VB6 era WYSIWYG designer built into Excel, plus some COM extensions for SMTP emails, with spreadsheets used both as a pseudo database mostly hidden from the user and as the output for rendering data but the actual logic is mostly VBA.

Excel's VBA IDE feels like a time capsule from 2002, and not in a good way (with some of the most comically unhelpful error messages I've ever seen and extremely aggressive syntax checking that constantly interrupts typing in code). Microsoft increasingly makes it very clear they don't want people using VBA but it still works fine in the latest PC versions from O365 after turning off a few security features/approving some prompts.

▲

stackghost 8 hours ago | parent [-]

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.

	▲	ewoodrich 8 hours ago \| parent [-]
		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.