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

LAMBDA(stationID, LEFT(RIGHT(stationID,LEN(stationID)-FIND(“-“,stationID)),FIND(“-“,RIGHT(stationID,LEN(stationID)-FIND(“-“,stationID)))-1))

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)

Developers could already write their own complex scripts with Visual Basic for Applications (VBA). The LAMBDA functionality extends a list of possibilities in which JavaScript has been added for more than two years. It is one of the other recent enhancements that includes support for dynamic data arrays: it is now possible to pass not a single value to a function, but an array of values. Functions can also return arrays of values. In addition, the data supported within cells is no longer just strings or numbers, but data types with a wide range of properties.

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.