The nightmare is real: 'Excel formulas are the world's most widely used programming language,' says Microsoft
So here's LAMBDA to make custom functions out of those formulas
Microsoft will let users create custom functions in Excel using the number wrangler's own formula language.
"Excel formulas are the world's most widely used programming language, yet one of the more basic principles in programming has been missing, and that is the ability to use the formula language to define your own re-usable functions," said Microsoft.
The addition is long overdue; those feeling a misplaced sense of déjà vu are likely thinking of functions implemented in the spreadsheet using an entirely different language, such as JavaScript or (heaven forbid) Visual Basic for Applications.
It also, according to Microsoft, makes the Excel formula language "Turing complete".
Dubbed LAMBDA, the feature (currently rolling out to beta customers) will be a lifesaver for anyone charged with maintaining herds of increasingly complicated spreadsheets, who have doubtlessly been wondering how it could be that Excel was missing such a seemingly obvious ability for so many decades.
Sure, a ribbon toolbar is nice enough (or should be destroyed by fire, depending on your perspective), but being able to drop Excel's own formula language into a custom function is both a huge time saver and a boon for quality.
While passing a value into a function is a handy thing, it is also possible to use dynamic arrays or rich data types. Devs expecting a Visual Code-like development environment for their opuses will, however, be a little disappointed. It's early days for the technology, and the implementation via the Name Manager is a little clunky.
There is also some serious power lurking beneath the surface. Recursion, long missing from Excel formulas up to now, can be achieved by allowing functions to be called within functions. What could possibly go wrong?
Not that one should be troubling production work with the new functionality. It remains in beta for the time being, and Microsoft has yet to indicate when it will hit General Availability.
It is also unlikely to trouble those legacy spreadsheets that could really use some tidying by the use of formula functions in place of the copy-and-paste workarounds users have endured for the over three decades of Excel's existence. ®