One thing that frequently comes to my mind - rather than trying to convince people to move to a general-purpose programming language (and ignoring all the reasons that people turn to Excel in the first place), is it possible to turn an Excel-style spreadsheet into a general-purpose programming environment that is powerful enough to do that sort of stuff?
People really underrate Excel as a programming environment, honestly. Yes, it's crippled and leads people to produce massive gross un-debuggable hellsheets ... but there's reasons (beyond just "it was the only usable software that could be run on office computers") that end users with a problem to solve keep turning to it despite those flaws. The combination of reactive programming, data-first visibility, no hidden state, decent approximations to structured programming by way of click-drag-and-copy-paste, and being able to reference variables and values without needing to name them has some kind of magic to it.
There's quite a bit of interesting research I've seen from Microsoft on how to take something like Excel and turn it into a non-crippled programming environment - spreadsheet-defined functions (including recursion, lambdas, and higher-order functions natively in the spreadsheet environment, without having to drop into VBA!), dynamic arrays, an alternate computation-first textual view that exists simultaneously with the data-first spreadsheet view, first-class complex data structures ...
Between HyperCard, Jupyter/Mathematica notebooks, and Excel, there's a lot of common features that seem to point to a vision of a truly useful end-user-accessible programming tool, something that would let people write real, useful programs intuitively and quickly enough to be worth building them themselves for whatever they needed them for, without forcing them into condescendingly-simplified drag-and-drop "visual programming" codeless clunkfests.
Excel is a general-purpose programming environment, it has a full programming language inside it (VBA) with the ability to call out to the operating system and load functionality through DLLs.
No, the way to tame Excel is to make it less general, to impose principles of structured programming on it. Separate code and data and presentation. Put some of the data in a database. Impose types on it, so Excel doesn't interpret genomes as dates etc. Make the control and data flow visible. Split it into pieces with defined responsibilities.
Excel is really a "two dimensional assembler": you put an operation in each cell, and they can read any other cell, and it all executes together.
In my first job out of Uni I wrote some VBA to make requests to the "online diary" to pull out some of the information into some template Excel files we had to create.
Some poor soul is probably still maintaining that.
People really underrate Excel as a programming environment, honestly. Yes, it's crippled and leads people to produce massive gross un-debuggable hellsheets ... but there's reasons (beyond just "it was the only usable software that could be run on office computers") that end users with a problem to solve keep turning to it despite those flaws. The combination of reactive programming, data-first visibility, no hidden state, decent approximations to structured programming by way of click-drag-and-copy-paste, and being able to reference variables and values without needing to name them has some kind of magic to it.
There's quite a bit of interesting research I've seen from Microsoft on how to take something like Excel and turn it into a non-crippled programming environment - spreadsheet-defined functions (including recursion, lambdas, and higher-order functions natively in the spreadsheet environment, without having to drop into VBA!), dynamic arrays, an alternate computation-first textual view that exists simultaneously with the data-first spreadsheet view, first-class complex data structures ...
Between HyperCard, Jupyter/Mathematica notebooks, and Excel, there's a lot of common features that seem to point to a vision of a truly useful end-user-accessible programming tool, something that would let people write real, useful programs intuitively and quickly enough to be worth building them themselves for whatever they needed them for, without forcing them into condescendingly-simplified drag-and-drop "visual programming" codeless clunkfests.