Thursday , October 6 2022

“Excel formulas are the most widely used programming language”, according to Microsoft, thus publishing LAMBDA For creating functions tailored from Excel formulas


Excel offers a host of functions that users can use on spreadsheets. However, the spreadsheet may not have a specific function required by a user with a specific need. It is therefore necessary to create it, which Microsoft Office suite software already allows through Visual Basic for Applications (VBA). Microsoft extends the list of possibilities with LAMBDA ?? a feature that lets you create reused 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 fundamental principles of programming is missing, the ability to use the formula language to define its own reusable functions, ”wrote the company Redmond . .

LAMBDA ([parameter1, parameter2, …,], a calculation to be performed) is the syntax of the new functionality available to users associated with the Microsoft Office Insiders program on Windows and macOS. Illustration of the GETLOCATION function defined as

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


Then one of the benefits of using LAMBDA becomes apparent: if touch is needed, only the personalized function needs to be modified. The effect is automatically reflected on all the cells that depend on it. Microsoft is compiling an additional list of benefits that include the openness to restarting LAMBDA functions. Illustration with the definition of the REPLACECHARS function which includes 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 function extends a list of possibilities to which JavaScript has been added for more than two years. It is in the list of other recent improvements that we find the support for dynamic data arrays among them: it is now possible to transfer not one value to a function, but a variety of values. Functions can also return arrays of values. Moreover, data supported in cells are no longer just strings or numbers, but data types that have a wide range of properties.

Excel also includes Azure Machine Learning support that users can rely on to use machine learning models for which data scientists, or other experts, in their company have developed. Machine learning functions are all based on a service that calculates or predicts values ​​with a machine learning model. After using the template, 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, type a cell, just as you would with any Excel function. The function calls a live web service on the company’s Azure subscription and returns the result asymmetrically.

There are many types of useful functions that developers can enable with Machine Learning, such as:

  • insightful forecasting, such as predicting a company’s future revenue trend based on time series data in Excel;
  • distribution issues for many Excel rows, such as detecting fraud from credit card transactions;
  • a tailored Python code, as a function of parsing text in the cell.

In short, Excel continues to acquire strengths. As a result of this situation, the latter is regularly in the midst of debates over its place as a platform for IT development and database management.

Source: Microsoft

And you?

What do you think of this new feature? What prospects do you see in the context of its use?
What do you think of Excel as a platform for IT development and database management?

See also:

Learn programming in VBA for Excel by doing – Part 5: protecting your sensitive workbooks, tutorial by Laurent OTT
Microsoft announces the end of support for Word, Excel and PowerPoint Viewer and recommends solutions available through Windows 10 and Office 365
Do you think VBA is not a professional language? Developer is trying out a group chat app with Excel and VBA

Source link