Excel needs exactly one thing to blow open the doors on productive programming: a new "function" data type. Since it's just a data type, you put it in a cell just like any other data type. Have some way to call it, like `A1(arg1, arg2)` or something. Now you can leverage the full capabilities of Excel to manage it, name it (named ranges), etc just like other data.
A more sane expression syntax might be nice too, but isn't necessary to make Excel more productive for abstract programming.
Sounds like you want to move VBA into the cell. I issue no opinion on doing that, just offering an observation.
Actually you make a great point (thank you!), but honestly I wouldn't want it to have anything to do with VBA.
For a feature like this to be successful imo it needs zero sandbox concerns; a user should never be prompted to downgrade to a lower security configuration, it has to be built in and available by default in every sheet. I don't see VBA ever being safe enough to enable by default everywhere. VBA is just an escape-hatch to a 'real' programming environment; my claim is that excel sheets & formulas alone could be a 'real' programming environment in its own right, no escape hatches necessary. All just by adding one feature to the existing formula language. The reason why excel is so popular today is its simple and understandable expression language and a unified spatially-oriented layout for code and data. Just shoehorning VBA into a cell loses all of the understandability of the simple excel formula and brings with it all the baggage of the old way of needing to manage source code. I think we can do better than that for normal everyday excel documents and normal users.
I completely agree and have been thinking the same thing for a while now. Another reason you need the function language to be really integrated with the rest of the spread sheet (unlike VBA) is so that global variables are also visible in the dependency tree and topological sort. For instance, imagine a cell containing the following lambda function:
It's clear that if the user changes the value in the A1 cell, any other cell which calls this function should be updated to reflect the new result. In addition to the arguments to the function (x and y), A1 is also an implicit parameter.
sounds like a great idea