If Excel had just a couple more features it could be much more ergonomic and not even need macros. Specifically: anonymous functions/lambdas made with just pure formulas. Define a function in a cell like `=($s) => [expr]` (or something idc). Call it with `=A1("string arg", B2:B100)` or by name if you name it via named ranges. Functions are obviously a new data type, so you can pass them as parameters to each other or to relevant builtins. It would turn Excel into some kind of hybrid pure functional/spatial language where the code itself is spatial data.
The typical things one gains from higher order functions are things like mapping and filtering of data structures. Excel only has one data structure—the table. Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away.
Functions add in questions of scoping. How are closures supposed to work? Could there be some way to say “the function in cell A1 at the time and closing over the state of when cell B2 was last logically computed”? Excel handles scoping in the non-function-in-cell approach using $.
Obviously this all breaks apart when you don’t want tables where you drag things either always down or always across
You jest, but people really do underestimate Excel. Yes, it sucks - but there is a reason that despite all its flaws, people keep trying to use it for things. (And no, that reason is not "they're too stupid to learn how to code")
And I'm not sure the things that make Excel so crappy could really be fixed without sacrificing the things that make it such a flexible and useful tool. For instance, adding the ability to recurse or iterate sanely would remove the transparency of having every iteration of a computation clearly visualized cell-by-copy-pasted-cell.
For me it usually is "I would like to use some sensible tools, but corporate policy requires me to not do that (because they are not available) - so I have to (ab)use Excel/Office/VBA to get things done." It's not pretty, stable, efficient or really maintainable, but if you are forced to use a hammer..
ps. Excel does some things OK, like drawing pretty graphs on smallish datasets and visualize quick analysis with pivot tables. It just lacks a sensible scripting language and is generally very brittle once the data is getting nontrivial and more than one person is involved.
> Yes, it sucks - but there is a reason that despite all its flaws, people keep trying to use it for things
Yes, but the main one is that institutional IT policies frequently dictate that people whose aren't employed specifically in software development roles aren't allowed software development tools, but everyone in the org tends to be allowed core Office apps like Excel.
When it's literally the only remotely applicable tool most people are allowed to have, it shouldn't be surprising that it gets used for a lot, independent of merit.
you can recurse in excel. its disabled by default but you can set the max depth on circular references in some menu. I haven't used it since the 2007 version
infogulch|7 years ago
dan-robertson|7 years ago
The typical things one gains from higher order functions are things like mapping and filtering of data structures. Excel only has one data structure—the table. Mapping is done by writing the formula once and then dragging it from the corner to the whole column. Filtering is not really done. Normally use the gui to hide the rows to be filtered away.
Functions add in questions of scoping. How are closures supposed to work? Could there be some way to say “the function in cell A1 at the time and closing over the state of when cell B2 was last logically computed”? Excel handles scoping in the non-function-in-cell approach using $.
Obviously this all breaks apart when you don’t want tables where you drag things either always down or always across
oomem|7 years ago
https://support.office.com/en-us/article/create-power-query-...
https://msdn.microsoft.com/en-us/query-bi/m/understanding-po...
https://msdn.microsoft.com/en-us/query-bi/m/power-query-m-re...
MrEldritch|7 years ago
And I'm not sure the things that make Excel so crappy could really be fixed without sacrificing the things that make it such a flexible and useful tool. For instance, adding the ability to recurse or iterate sanely would remove the transparency of having every iteration of a computation clearly visualized cell-by-copy-pasted-cell.
skriticos2|7 years ago
ps. Excel does some things OK, like drawing pretty graphs on smallish datasets and visualize quick analysis with pivot tables. It just lacks a sensible scripting language and is generally very brittle once the data is getting nontrivial and more than one person is involved.
dandelany|7 years ago
dragonwriter|7 years ago
Yes, but the main one is that institutional IT policies frequently dictate that people whose aren't employed specifically in software development roles aren't allowed software development tools, but everyone in the org tends to be allowed core Office apps like Excel.
When it's literally the only remotely applicable tool most people are allowed to have, it shouldn't be surprising that it gets used for a lot, independent of merit.
ta_egdhs|7 years ago
dabei|7 years ago
analog31|7 years ago
hef19898|7 years ago
cm2187|7 years ago
MaxBarraclough|7 years ago
(I was surprised to find 0 results on Google for this term.)
swingline-747|7 years ago