3 ms·
This 100%. My hell for a couple years was being an Excel forms "developer" as part of my job duties, and maintaining an extremely complex Excel spreadsheet (tha
by 4oo4 4y ago
This 100%. My hell for a couple years was being an Excel forms "developer" as part of my job duties, and maintaining an extremely complex Excel spreadsheet (that should have been a web application) that I inherited from my predecessor. I wish I had taken screenshots but I think some of the most complex formulas in it had about a dozen levels of branching in them, and to make it worse it was used for a business critical process related to documentation requirements for consumer mortgage loans. The formula shown in the screenshot is representative, though to make it more manageable you can hide and label intermediate calculations in hidden columns and sheets. It looks like they may have done some of that based on the cell references, but they could still offload a lot more of that to different places.
There were days I spent several hours clicking "Evaluate Formula" button close to 100 times in a row to debug things like this. The syntax colorization definitely helps, but I would have killed for just the ability to pretty print a formula or set a breakpoint. I'm always shocked it doesn't have features like this that programmers depend on, but I wonder if part of that is because the people that would write something this complex in Excel have never experienced all the tools you get with "real" programming languages.
At home I was getting deeper into Linux and bash/python, and that made me resent Excel even more. I finally moved into the IT department and got really into PowerShell and training myself as a sysadmin, but I would still get requests about that damn spreadsheet for at least a year or two until they finally stopped asking. I used to enjoy problem solving challenges with Excel for a while, but in the end it became a parasitic relationship because of the time needed to fix issues and the complete lack of ownership and knowledge within the department that supposedly owned the form and process.
Critical decisions are made based on Excel formulas like these, (people getting fired, financial decisions, etc.), maintained by people that barely understand programming. Yes, it's nice that it's helped democratize automation and coding for non-technical people, but Excel just wasn't designed for things this complex. When your Excel formulas and macros start to look like this, you need better tools to manage the complexity and trust the maintenance to tech people instead of business people that don't fully grasp the monsters they're creating when they just keep using the only tool they know.
- HPsquared 4y agoThere are some tools available to pretty print Excel formulas, e.g. https://www.excelformulabeautifier.com/ https://www.excelformulabeautifier.com/