What do I really think about LAMBDA?
I’ve been writing about the LAMBDA function, which brings the ability to create custom formulas in Name Manager. To continue with this now third post on the topic, I’ll address distribution, the Advanced Formula Environment (AFE), and a recap with a final word on LAMBDA use.
I admit that I stepped with cautious glee into the world of LAMBDA functions. As a geek I do like to play with new toys, even to the point where they break. At the same time, I look towards application. To invite user-made functions must mean there’s a kind of break point, where there are a sufficient number of “formulas” in use – combinations of built-in “functions” and inputs – that are frequently enough used to warrant the deployment, training and governance of a custom formula tool.
The idea that we need this new thing must not be just as a new way to write formulas, but to build the most reusable functions possible, for real productivity gains. The idea is to "function-ize" formulas that are typed in over and over again. But a key benefit of LAMBDA, and of Name Manager in general, is translation from Excel to local language. A LAMBDA doesn't need to be very complex to be welcomed as an intuitive tool.
I presumed that market demand had again produced useful innovation, and experience confirms. But like many new products, LAMBDA undershot its market. It works great for someone like me, who likes to collect, learn and organize tools even if they are not presently needed. The greater market is the mass of users who will not invest in that level of specialized capability, but who can appreciate and benefit from solutions that are well presented, and the organizations that support them. This is the promise that sells me.
In this mass market, Name Manager comes out of obscurity and becomes a background dictionary of tailor-made functions. The user need not know or care about Name Manager to use the dictionary, except that they must somehow take delivery of it, and be made aware of its lexicon. There must be some way for a provider to distribute, and for a consumer to install or transfer names to new and existing files.
When names represent range aliases, there’s no need to copy them between files. Ranges pertain only to the sheet and book in which they reside. In fact, range names copied with a sheet can be a nuisance when they interfere with the destination names, or when they retain links back to the source file.
But with LAMBDA comes a class of names that are defined generically, that is, with no reference to any actual cell address, which can be copied and used in any Workbook. But a name must exist in the Workbook in which it is applied, unlike VBA routines that can be run from any “ActiveWorkbook”.
So a key new feature requirement for the larger market is transferability, and this is what was apparently overlooked, or planned for later release.
Copying Names
There are several ways to copy Name Manager definitions between files. Unfortunately, of the six methods I’ve tested, they all copy the names well enough, but only two copy the comments as well as the names and definitions. The omission of comments from the other methods is undoubtedly a bug that will be fixed at some point. Meanwhile, the more we become invested in Name Manager as a resource, the more critical the comments are, and their loss cannot be tolerated.
To briefly recap why comments are so important, I showed in the previous post how the name comment pops up as the function description while browsing the in-cell type-ahead list. Especially if the argument list does not appear, as sometimes happens (also detailed in that post), the comment is an essential point-of-use help. It is also a way to sort names by some logical grouping. See namespaces below for another way to do that.
In the table below are the six methods tried. In method 1, the names are defined in the active file, at Workbook scope, and any sheet tab in the file is copied to another workbook, carrying the names with it. Methods 2-5 are all based on having saved a template file; 2-4 in \XLSTART, 5 in \Templates. For more information on templates, click here. Method 6 is a new feature explained further below.
What’s AFE?
The Advanced Formula Environment (AFE) is a free add-in provided by the “Microsoft Garage Project”, available at Developer – Add-ins – Store. It opens a side pane where existing names appear and can be edited, or new ones added.
The editing features may be helpful for some, but before jumping in, know its limitations. You may quickly come to realize why it’s called a “garage project”. The most glaring omission is that name comments are nowhere to be found. One must wonder, why didn’t they just upgrade Name Manager, rather than downgrading it in a new interface?
Briefly, AFE has the following features
Manager mode: create, edit, rename, delete, share one name at a time.
Create Range name: starts with the active selection (absolute, F4 doesn’t work)
Create Function name: starts with “= LAMBDA(x, x)”
Create Formula name: starts with “=”
Editor mode: lists all names sequentially, semicolon-separated
Can create new namespaces
Can add developer comments in format: /* comment */
Ctrl+A, Ctrl+C to copy full list to new workbook or to text file
Import options
From GitHub Gist URL
From text “snippet” (paste names copied as above)
In Editor, paste names directly
By my count, AFE becomes the fifth formula editor, after the sheet, Name Manager, Conditional Formatting and Data Validation. To become more familiar with the first four, see my post here for Name Manager overview, and explanation of the various behaviors of the four different formula editors. Then decide if you want to learn the fifth.
Personally, I like to write formulas in-cell or in the formula bar, multi-line if complex, where syntax help is given and input and output errors are exposed. I can then copy them to Name Manager. Remember, the trick to writing LAMBDAs in the sheet is to provide the arguments in parentheses at the end.
A note on “function” and “formula” (overview here). When you insert what AFE calls a “function,” you are actually writing a “formula,” using LAMBDA and other built-in “functions”, to create your own “function,” which can then be used in “formulas”. Confused yet? Just remember, a formula is made up of one or more functions. A function is elemental, it cannot be broken down.
Anyway, the point of reviewing AFE here is transparent exposure, and to judge its import feature. The bottom line is, without name comments, no sale. I did test posting my LAMBDAs on GitHub, Gist import can be done by copying the URL here. Another set is provided by Chris Gross of Microsoft here, with LAMBDA and AFE overview here.
While testing the AFE import feature, it asked me if I wanted to add the formulas to a new namespace. I wasn’t sure what that meant, so I tried it, entering a namespace name when prompted. It imported the names preceded by the namespace, dot-separated: namespace.name. This offers another way to sort and organize names, with the possible advantage that when entering the function in a cell, you can start with the namespace, thus focusing your list on the specific group of related functions. This is also a way of avoiding collisions with existing names, if you decide to copy or import. Namespace-prefixing can be done when manually creating names as well, to preserve comments.
The Solution to Retain Comments
So of the two methods (4 and 5) that copy comments with names, which one to use? Think about how you or others will use your set of LAMBDAs. Do you want them to always be there, in every new file created? Or do you have one or more specialized sets of LAMBDAs?
To apply them to every new file, use method 4. Save the template as ...\XLSTART\Book.xltx, and every Ctrl+N will create Book1, etc. with the names and comments intact. To apply them with specific purpose, use method 5. Save the template as ...\Templates\LAMBDA.xltx (for example). Your normal Ctrl+N books will not have the names, but when you need them, use File-New and select the template you saved (pin it for easy access).
Unfortunately, these methods work for new files, but not for existing files. For existing files, one option is to use any of the other methods to copy or insert or import the names, and suffer without the comments. Another option is to create a new file with method 4 or 5, and copy the sheets to it from the existing file. Watch out for Name Manager conflicts.
Of course, a VBA macro can copy the names and comments to any file, and this suggests a possible work-around solution for those who are VBA-capable, but that is not optimal and not for everyone. I do have a macro in Startup.xlsm called ListNames (free code here). This macro creates a new sheet with a table of names. In addition to name and reference, the macro adds scope, comment, and derived columns, and also a feature to upload name comments from the sheet to Name Manager. This macro is helpful to review names and to edit comments, but does not address inter-file transfer. One caution, do not sort the table before uploading comments.
LAMBDA Recap
If I take the role of coach, I challenge you to “excel” in Excel. That means using the right features for the right reasons, with simplicity bias. New features like LAMBDA and AFE come with enticing sizzle, but do not always add enough value for the added complexity. The complexity needs to be exposed by taking a long-term view of what current losses can be eliminated and what costs and risks will be incurred, with a realistic assessment of user adoption.
We must also presume that Microsoft will continue to improve the product. If anyone asks, here's my wish list:
enable easy transfer of names with comments into any file (must-have)
enable optional arguments
include LAMBDAs in the Insert Function dialog (in "user-defined" or a new category, where comments provide the function description, and even, somehow, argument descriptions)
fix bug causing argument list not to open
If you are in a position to influence organizational adoption and behavior, stay ahead of users. Join Office Insider and subscribe to media that promotes innovation. Then, make time to learn and test new features, how they might be used, then get input on how they could or should be used. If you decide to adopt LAMBDAs, consider both centralized development and deployment, and training to empower users. Like templates and macros, LAMBDAs will improve from competition to find and promote the best-in-class, but can proliferate and devolve if left alone.
For training, there are good examples on YouTube, and here are a few points of emphasis.
Write LAMBDAs to work stand-alone, independent of other names or ranges. Those can be used at point of entry to feed the arguments.
Use full words as arguments in LAMBDA and in LET for best transparency.
Make flexible LAMBDAs by including a “type” argument with conditional output.
Comentários