May 02 2022 08:36 AM
May 02 2022 08:36 AM
I've only had access to the LAMBDA function since February, but I've definitely fallen in love with this new feature. The more I learn, the more potential I see. I'm at the point now where I'm starting to hit some of the limits of the function, the functional language, and the tools that support it. I want to share my thoughts and very interested in hearing yours on these topics.
My Lambda Wish List
1. Lambda Editor/Debugger - The new AFE is a tremendous leap forward from dealing with Name Manager. But as I work with increasingly large complex functions, it's way too hard to debug in AFE. My dream would be a UI more like Power Query you can click on any variable of a LET statement and see the evaluated result of that step. I work around this by developing complex lambdas on the worksheet and rolling them up to a lambda at the end. Charles Williams has a Lambda Explorer available that has some of the right ideas, but you can't edit and the bigger the lambda gets, the slower it gets (see Lambda Performance below). Note: my largest lambda is over 5,000 characters.
2. Lambda Package Manager - Sharing lambdas with others using Gists is such a great idea. It works great even for complex gists up until the point that dependencies come into play. The real potential of lambdas comes from using other people's lambdas as building blocks for your own. If I want to build a lambda using someone else's as a basis, I either have to copy it into my gist or provide instructions to make sure to also import that other gist. But that other gist might have a bunch of lambdas in it, and I only use one. And the versions can change over time. We need a Conda/NuGet like package manager that can keep track of dependencies, notify you when a newer version of an imported lambda is available, and a way to import just what you need to support the specific lambda that you want.
3. Multi-Developer Support - Gists only have one owner and don't make it easy for multiple people to contribute to a single Lambda collection. It would be great if there was support for maintaining an entire collection of lambdas in a shared GitHub repository where each lambda could be in a separate file in the repo but could be imported into AFE either individually or as an entire collection.
4. Lambda Performance - Based on my experience, you can create very large complex lambdas and they perform surprising well as long as there are either no dependencies or very few dependencies. If you have a collection of lambdas that build on top of each other many layers deep, the same functional logic takes significantly longer to evaluate. For the functional language to reach its full potential, Microsoft needs to find a way to improve this.
5. VBA Lambda Evaluation - While Application.Evaluate works in simple cases to evaluate a lambda function and return the results into an array, as they get more complicated, the Evaluate function gets increasingly unreliable, either failing or even worse, returning incorrect results. This makes it very hard to create solutions that combine the power of lambdas with the power of VBA. My hacky workaround at the moment is to put the function in a cell, capture the spilled range to an array, and then remove the formula from the cell. I would love to get a fix for this. (Note: I'm on GA Current, so it's possible Beta already has some fixes that I'm not aware of.)
6. Lambda Discoverability - As part of my obsession with lambdas, I've been scouring the internet looking for examples of cool things people have been doing with the LAMBDA function. With the help of David Hager, we've found a TON and are working to curate them in a way that will be useful for others and give credit to the original sources. But they shouldn't be this hard to find! Even if everyone got in the habit of sharing them as Gists, when you search for Lambda across gists you get a ton of AWS and other lambda code snippets unrelated to Excel. I wish the AFE supported assigning curated metadata (like document properties) where a lambda can have a friendly name, function description, parameter descriptions, links to documentation, and credit to the author or source where it came from. This metadata should stick with the lambda as it is imported, exported, and copied around, and be accessible via the function's tooltip. But more importantly, there should be an easy way to search for lambdas and lambda collections either within AFE or in Excel itself.
7. LAMBDA Cells - Why can't I put =LAMBDA(x,x+1) in cell A1 and then refer to it in A2 as = SCAN(SEQUENCE(10),A1)? Or at least let me create a named range AddOne that points to A1 and allow me to use that named range as a function, ie: =SCAN(SEQUENCE(10),AddOne), or =AddOne(SEQUENCE(10)). And don't show me a #CALC! error in that cell. Show some kind of icon indicating it's a LAMBDA cell and if you point at it, show the name, description, and parameter list it requires.
I know this is not in the control of AFE or Microsoft Research team, but putting it out there in case it's something you have some influence on.
8. Visual Studio Code Extension - Please turn the AFE editor into a Visual Studio Code extension so I can work on my Gist lambda files with the same nice Intellisense, automatic indenting, and code formatting.
9. Lambda Generator - Allow me to specify a range or sheet along with input cells and one output cell, and generate the lambda for me by taking all the dependencies from the input cells to the output cell and building the LET and LAMBDA statement automagically. This is something that I'm trying to build a tool for right now and it's coming along. (Let me know if anyone wants to volunteer to help test it before I make it freely available to the public.)
10. Lambda Web Service - And lastly, since this is a wish list, I would love to have the ability to publish LAMBDAs as web service in Azure so Excel can be used as the calculation language, and those calculations can be used outside of Excel, in web apps, mobile apps, and Power BI.
I know this is a lot to wish for and many of you are thinking I'm crazy. Some of you will want to lecture me about using the right tool for the right job, yada yada. I know I can create UDFs with VBA, XLLs with VSTO/ExcelDNA/C SDK, measures in DAX, transformations in M, Office Add-Ins with TypeScript, Python scripts with pandas in XLWings and PyXL, etc, etc. They all have their places, but Lambdas are awesome, and I am excited about the possibilities.
Which of these get your vote??? What's on your wish list for LAMBDAs?