Microsoft has fixed the big hassle that comes with the otherwise excellent Lambda() feature in Excel 365 with the new Advanced Formula Environment.
Office Watch has sung praises over the power that Lambda brought to Excel. It’s the ability to write custom functions within Excel but without VBA macros.
Proper IDE for Excel formulas
But we also noted that it was hard to write decent Lambda() code because the Excel formula interface wasn’t up to the task. Lambda() has the potential to be multi-line code but that’s hard to manage in a single line like this:
When Lambda was released, Office Watch said:
“What Excel badly needs is a proper IDE for formulas that’s fit for the 21st Century. “
Instead of that single line you can now see Lambda() formulas with all the stuff you’d expect in a modern code interface like color coding, indenting, inline errors, comments and autocomplete.
Advanced Formula Environment
A fix has arrived, welcome to Excel’s Advanced Formula Environment . It’s a free Excel add-in from Microsoft Garage as part of the Excel Labs bundle.
Features
“ Common features that make programming easier, such as immediate inline errors and syntax highlighting, are missing. The advanced formula environment is an interface for the Name Manager designed for formula authoring. Using this add-in you can:
- Write named formulas using an editor that supports inline errors, intellisense, comments, and more.
- Indent formulas, making them easier to read.
- Edit all named formulas using a single code editor.
- Quickly reuse LAMBDA formulas by importing them from GitHub gists, or by copying them for other workbooks. “
Get Advanced Formula Environment
Install Advanced Formula Environment via the Office Store to work with Excel 365 for Windows, Mac and Web also Excel 2021/2019 for Windows/Mac.
Let’s hope this doesn’t just sit in the Garage, Microsoft’s test area, and graduates to full Excel app soon. In the meantime, use the Advanced Formula Environment to make bigger and better Lambda().
Lambda – strange name for a really good extra in Excel
Lambda() downsides need to be fixed
Understanding the new Lambda helper functions
