I once had a project to turn a customer’s Excel VBA application into a real application (I used ASP.Net). He had been hacking on this Excel spreadsheet for like 15 years. Once I printed the VBA code because it was hard to navigate and it was like 250+ pages printed out rather compactly.
The worse part wasn’t the code itself (although it was bad), but the fact that there was so much abandoned paths in it and there would be three or more different versions of crucial functions with the same name (or sometimes a different name but doing the same thing) in different places and sometimes all being called by different paths in the workflow. Or not being called at all.
And it was very math heavy (calculating natural gas pressures and flow rates through different sized pipes and fittings and sizing regulators to meet certain loads). Think Excel formulas on cells that referenced 15-20 other cells, each of which was a formula on their own that referenced other pages and cells, some of which were filled by VBA. And that wasn’t even involving the VBA code full of brute force solvers for multi-variable equations that used heuristics he’d worked out by trial and error (if it’s a delta over 1.5, add 5 to this variable, otherwise subtract 3, but if the delta was less than 0.5, add 1 and so on - it eventually converged or found no solution, but a binary solved did the same thing, only faster and easier).
It took me and a junior developer several months, during which, of course, multiple change requests were going through to make it multiuser and secure.
Both my nightmare project and one that I’m very proud of once it was completed.
That's more adventurous than all the Excel projects I've been given. Many of the most satisfying solutions are the most simple that nobody's implemented yet.
Like, at a job where all the lead devs and, apparently, the whole internet agreed that you can't do proper source control for Excel because you can't export-and-import code for all modules (including sheets & ThisWorkbook) without copy-paste and because a running module can't replace itself. The solution ending up so simple, that I was embarrassed to hear it called "a stroke of genius". I still have that code somewhere.
The worse part wasn’t the code itself (although it was bad), but the fact that there was so much abandoned paths in it and there would be three or more different versions of crucial functions with the same name (or sometimes a different name but doing the same thing) in different places and sometimes all being called by different paths in the workflow. Or not being called at all.
And it was very math heavy (calculating natural gas pressures and flow rates through different sized pipes and fittings and sizing regulators to meet certain loads). Think Excel formulas on cells that referenced 15-20 other cells, each of which was a formula on their own that referenced other pages and cells, some of which were filled by VBA. And that wasn’t even involving the VBA code full of brute force solvers for multi-variable equations that used heuristics he’d worked out by trial and error (if it’s a delta over 1.5, add 5 to this variable, otherwise subtract 3, but if the delta was less than 0.5, add 1 and so on - it eventually converged or found no solution, but a binary solved did the same thing, only faster and easier).
It took me and a junior developer several months, during which, of course, multiple change requests were going through to make it multiuser and secure.
Both my nightmare project and one that I’m very proud of once it was completed.