Today we look at the November 2022 release of the Advanced Formula Environment, AFE 1.1. While I have tolerance for a garage project, I have none for a bad release. An "upgrade" should always be on the plus side of the experience spectrum; ideally delightful, at worst neutral. I'm sad to say that even though this release delivers some improvement, the overall experience is a net negative.
Innovation and Change Management
In organizations there's a close partnership between innovation - the conception and articulation of improvements; and change management - the construction and delivery of those improvements. There's also a governance role to ensure that the most productive tools and practices are installed and utilized. These roles may be consolidated or specialized depending on the size and design of the organization.
To keep their budget and skills alive this improvement function must stay active, even when the customer is not seeking and not receptive to new solutions. Accounting of costs and benefits is impractical, and there comes a subjective point of diminishing returns. From a user's perspective, once the engine is tuned you have to get it out of the garage and on the road; further change is an obstacle. As an innovator then you have to work on what you think the customer can be enticed to adopt. You begin to drift from relevance, and release profit dwindles. Credibility and trust are diminished.
Reasons for this disconnect are organizational, despite the best intentions and efforts of the players. Here are some that came to mind while evaluating the new AFE release:
obfuscation: "telephone game"
push engineering: "solution in search of a problem"
push marketing: "customer must appreciate us"
saturation: "change fatigue"
Add it up and you have a very slim margin of success.
I am an eager early tester, like a fact-checker, to confirm or deny the value statement put forth. As I wrote last week, it's like opening Christmas packages. The surprise can be pleasant or a let-down. It can be grueling work, but I keep going for curiosity, and for you. The mission of xlegant is to turn down the distraction and turn up the attraction, to promote principles, fit tool to purpose, and improve efficiency and effectiveness.
I think you'll agree that AFE deserves the rant, and with this context we can understand it. In previous posts several months ago, I gave my views on AFE, and the LAMBDA/LET functions that it specially supports. This feature package still shows great potential, and garage clutter. Recap of early posts:
AFE 1.1
The Good
Release notes! Copied here for reference:
AFE 1.1 (November 2022)
AFE 1.0 (February 2022)
Comment synching with Name Manager
Comments are written in /**comment*/ format preceding the name definition. After synch (Ctrl-S) the comment appears in Name Manager and in cell entry:
The Promising But Unproven
Import from grid
Intriguing but clunky, and zero help. If you're already comfortable writing functions with parameters this might not help anyway.
Table reference with autocomplete; I have not seen this work, and it's unclear how it would since the square bracket notation for table fields is the same as for optional function arguments. Hopefully I'm missing something, will let you know.
The Bad
The first cardinal rule of change management is "do no harm." Sometimes you fix a bug only to create another one, or you neglect to pave the way for smooth adoption. Obviously these effects are inadvertent, but they need to feed back into a non-negative-impact testing plan.
Non-portable: "Modules replace namespaces," but when I upgraded, existing namespaces were NOT converted to modules; all functions dumped into Names-Functions, and Modules was blank.
Transfer from Functions to Modules is painful! - see steps and image below.
create new module
type function name or copy-paste from Names-Functions to Module
type arguments in Module (no way to copy)
copy-paste code from Names-Functions to Module
delete function from Names-Functions
Name Manager syntax is strictly enforced in Module names, when it was not in AFE 1.0 Namespaces: a module name cannot be "c" or "r", or begin with prohibited characters (only letters, _, \ allowed)
Function names within a module cannot contain a period, even though AFE 1.0 allowed it, and Name Manager allows multiple periods in a name. The dot is apparently now reserved as module.function separator.
Names not synchronized from Name Manager: in the example file below, there are many named formulas, none of which appear in AFE.
Modules synch 1-way, but only if changed in AFE after a change in Name Manager. For example, if you edit a comment in Name Manager then synch AFE, it will not revert the change unless you change something in AFE.
Names synch 2-way but doesn't work unless you close and reopen AFE.
The Ugly
Let me count the ways...
Tabs: 3 main tabs,
Grid tab: Why? I go back and forth from sheet to AFE frequently. If I want to edit a cell, I will do it in the sheet. The only use I can think of for the Grid tab is if I'm writing a lengthy formula in a cell, which would be helped by AFE formatting features - but I'm using AFE to avoid lengthy cell formulas...
Names tab: 4 sub-tabs. Why so much separation? It seems that AFE is trying to take market share from Name Manager, but it will fail. It should stick to its niche of lengthy custom functions (as in Advanced Formula Environment...)
Names-Functions: collects all LAMBDA names
Names-Ranges: collects all range names, except #-referenced arrays
Names-Formulas: collects non-LAMBDA, non-range names including array constants and named array references, e.g. name=Sheet1!$D$10#
Names-Modules: list of modules with two action buttons: copy module to clipboard and edit module, which takes you to the Modules tab:
Modules tab: create modules; type/paste/import functions; with a module selected, rename or delete the module. Notice the illogical separation of duties between Names-Modules and Modules.
"friendly" function editor (above)
error message persists: AFE flags name and syntax errors and raises an error banner upon synch. When the error is corrected and re-synched, the banner remains until manually closed.
What's Still Missing
Singularity. Name Manager and AFE are still vying for attention. How long will there be two doors to the same room? The fact that AFE is an add-in indicates that it's still early stage, and if/when matured enough will be incorporated into core Excel.
Reach. Custom functions have evolved into general tools, with no specific reference to the sheet or book in which they reside. Yet, as names they must reside in the book in which they are used; hence the awkward import/copy process. By contrast, with VBA, a file in the XLSTART folder opens with Excel, can remain hidden, and provides access to its routines from all open books. Sometime, somehow, Excel must address the "residence requirement" of globally applicable functions.
Argument help. Custom functions should be able to provide picklists for indexed arguments, like native functions do.
Function help. Custom functions should be able to package a help file together.
My Vision
When I think ahead to what future Excel might look like, there will be no more need for an add-in. Name Manager will evolve to incorporate the best of AFE; the sheet formula bar itself might even incorporate AFE formatting and enable draft save and direct save to the manager. Name scope options will add Application to the Workbook and Worksheet scopes, as the default scope for custom function names; a file in XLSTART like PERSONAL.xlsb will make its Application-scoped names accessible in any open file. Name modules will be export/import text files like VBA modules. Custom functions will be able to assign categories, define argument picklists and connect help files, like native functions do, and will appear in the Insert Function dialog.
Bottom Line
The lack of function comment transfer was my first and biggest complaint of AFE 1.0. That now works and is worth the pain of manual transfer to modules. I don't blame the AFE team for it not having all the features I want; I blame it for the net loss from what should have been a net gain. Anyway, we should continue to use AFE Modules to build functions and try not to get distracted or frustrated with its quirks.
Coming Up
In upcoming posts I'll show off some new LAMBDAs that incorporate my latest acquired techniques; I'll also be working on some new and old practical applications. Let me know what you'd like to see! Cheers.
Comments