|
Listen to this story
|
With the introduction of LAMBDA, Excel has now become a full-fledged programming language. According to Microsoft, Excel is Turing-complete, and users can write any computation in Excel formula language.
Excel Is Turing-Complete
Microsoft Excel was first introduced in the 1980s, and since then, it has become the go-to platform for organising, analysing, and visualising data. Excel is the most widely used programming language, and its formulas are used more than C, C++, Java, Python combined, Microsoft claimed.
For the longest, Excel suffered from two main shortcomings:
- Excel supported only scalar values such as numbers, strings, and Booleans
- It did not have the provision for user-defined functions
Microsoft Research’s Calc Intelligence project led the transformation of spreadsheet formulas into a full-fledged programming language. The first development took place at the 2019 ACM SIGPLAN Symposium on Principles of Programming Languages (POPL 2019). Microsoft made two major announcements: to have data values beyond just text and numbers and to allow cells to include first-class records; and allow the use of ordinary formulas to compute complete arrays.
Building on this, Microsoft soon announced the beta version of LAMBDA. Previously, Excel allowed user-defined custom functions, but they had to be written in languages such as Javascript. On the other hand, LAMBDA allows the users to define their custom functions using Excel programming language. It also allows function calling.
Turing complete (a concept derived from theoretical computer science) means the programming language is powerful in terms of performance, maintenance and richness of the ecosystem. A programming language is Turing complete when users can implement any algorithm with it.
What To Expect
Formula reusability: One of the challenges in working with formulas in conventional Excel programming language is they are often very complex. When these complex formulas are reused repeatedly throughout the sheet, there are mainly two types of roadblocks:
- If there is an error in the logic, programming would need to go back and update it everywhere. The risk amplifies when the formulas used are complex — giving way for greater human error.
- Secondly, for a programmer who is not the original author, it is hard to comprehend the intention of a given formula.
Any formula built in Excel can be wrapped in a LAMBDA function and used anywhere throughout the sheet.
Recursion: Excel formulas cannot loop (repeat over a set of logic within defined intervals). Users generally configure the time interval manually. However, given the set of characters we work with are not static, the manual way is not always suitable and may lead to added complexity and errors. However, with LAMBDA, users can create a function called REPLACECHARS. It references itself and allows the user to iterate through the list of characters to be removed.
Different Data Types: This is a general improvement, but Excel, over the years, has introduced new types of data that can be worked with. It includes dynamic arrays for passing values and functions; and inclusion of other rich data types for values stored in a cell.
Wrapping Up
In the future, Microsoft Excel would have nestable arrays and implementations of array-processing combinators that take lambda functions as their arguments.
Microsoft also plans to define functions through sheet-defined functions. Meaning, users will be able to define larger functions using multiple formulas spread across cells.
