But writing complicated macros to encode business processes is dysfunctional.
It is far better to have separate programs in Python, Java, or VB that pull the data from the spreadsheets, and are subject to code reviews and source control.
Having been on both sides of this, this is a lovely idea in theory that often fails in practice. Having to rely on IT developers to write business-critical software in a timely manner is either cost prohibitive or takes far too long.
A good example from my own experience was from working at a large footwear & apparel company during the outset of the pandemic. Suddenly there were huge port congestion issues at various ports in Asia causing the system-predicted lead times to suddenly be far too short. The system didn't have the logic to match up specific ports to specific factories to be able to tell which products would be impacted by which ports. The lead-time tables in SAP were only at an "entire country" level. This meant that all the coverage and supply allocation logic coming out of SAP was tragically wrong. Even worse the port congestion was rather fluid and dynamic (e.g. we know it's 2 weeks this week but are predicting orders arriving next week will add 3 weeks), where our install of SAP assumes such lead times are fairly static.
We couldn't wait a year for IT to come up with something (and certainly didn't have the $10million IT quoted to partially fix the problem). So what did we do? Set up processes to download the data into Excel and augmented it with the factory x port x destination transit time information, and reproduced the reports that were normally coming out of SAP. The non-IT business users had this worked out in just a week and managed to run a multi-billion dollar business this way for nearly 2 years.
Was it ideal? No. Did it work? Yes? When did IT actually build this capability into SAP? July of 2022. We'll be ready for the next pandemic, I suppose, as long as we can afford to have these changes ported into the next version of SAP we have to upgrade to.
This is why Excel remains to prevalent and non-IT users build so much logic into their workbooks - it's either that, or do it by hand.