Microsoft has fixed the big hassle that comes with the otherwise excellent Lambda() feature in Excel 365 with the new Advanced Formula Environment.
More than a year ago, Office Watch sung praise over the power that Lambda brought to Excel. It’s the ability to write custom functions within Excel but without VBA macros.
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:
Well over a year ago, 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 finally arrived, welcome to Excel’s Advanced Formula Environment . It’s a free Excel add-in from Microsoft Garage.
“ 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. “
Install Advanced Formula Environment via the Office Store to work with Excel 365 for Windows, Mac and Web.
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().