Excel offers a multitude of functions that users can make use of on spreadsheets. However, the spreadsheet may not have a particular function required by a user with a specific need. It is therefore necessary to create it, which the software of the Microsoft Office suite already allows through Visual Basic for Applications (VBA). Microsoft is expanding the list of possibilities with LAMBDA – a feature that allows you to create custom, reusable functions from Excel formulas.
“Today, we are providing our beta customers with a new functionality that will revolutionize the way we build formulas in Excel. Excel formulas are the most widely used programming language in the world, but one of the basic principles of programming is missing, namely the ability to use the formula language to define its own reusable functions, ”writes the Redmond firm. .
LAMBDA ([parameter1, parameter2,…,], calculation to perform) is the syntax of the new function available for users linked to the Insiders program of Microsoft Office on Windows and macOS. Illustration with the GETLOCATION function defined as
One of the advantages of using LAMBDA then emerges: in the event that a touch-up is required, only the custom function needs to be changed. The impact is automatically passed on to all cells that rely on it. Microsoft lists an additional list of advantages, including the recursion of LAMBDA functions. Illustration with the definition of the REPLACECHARS function which includes a reference to the same function:
REPLACECHARS =LAMBDA(textString, illegalChars, IF(illegalChars=””, textstring, REPLACECHARS(SUBSTITUTE(textString, LEFT(illegalChars, 1), “”), RIGHT(illegalChars, LEN(illegalChars)-1)
Excel also includes Azure Machine Learning support that users can rely on to use machine learning models that data scientists, or other experts, in their organizations have developed for them. Machine learning functions are each based on a service that calculates or predicts values with a machine learning model. Once the template is deployed, authors can activate it for anyone they choose.
Within an organization, administrators can configure who has access to proprietary functions. Then, whenever someone wants to run the function, just tap on a cell, just like you would for any Excel function. The function calls a live web service on the company’s Azure subscription and returns the result asynchronously.
There are many types of useful functions that developers can enable with Machine Learning, such as:
- smart forecasting, such as predicting the trend of a company’s future revenue based on time series data in Excel;
- classification issues for many Excel rows, such as fraud detection from credit card transactions;
- custom Python code, such as an in-cell text parsing function.
In short, Excel continues to acquire strengths. Because of this state of affairs, the latter is regularly at the center of debates in connection with its place as a platform for IT development and database management.