Forum Discussion
Is it time to revisit the Defined Name? I believe 365 requires a renaissance of the Name.
I just saw Andy and Jack's presentation - lots of developments in Lambda Editor, I like the combined editor / name manager views and also the connectivity with github.
I believe the ability to define functions from cells could also enhance user experience especially for those less familiar with coding environments. The recent smooth scrolling update will help working with longer formulas in the grid too.
This github link contains an xlam addin containing two functions:
DEFINE( [Scope], [Visible], [Type], [Category], [Comment] )
DEFINITIONS( [ShowDetails], [ShowHidden] )These functions allow one to define and view names created via LET formulas.
As a simple example, after installing the addin, enter the formula below in a cell of a new workbook,
=LET(π, PI(),DEFINE())Then re-enter without the name definition to remove the name.
Some further examples are shown in the screenshot below.
Making the functions global, so as to be available from any workbook, proved pretty challenging. It's also designed so that workbooks containing these functions can be distributed to other users who do not have the addin installed. No doubt further updates will be needed to iron out any remaining issues.
Apologies for pre-empting you; it was simply a case of seeing your suggestion for a new thread and deciding you were correct. It might well have been better if you had got in first, I might be developing a dubious reputation promoting arcane ideas and generally 'rocking the boat'!
1. Name Manager. We are all familiar with this. As a formula development environment it offers an extraordinarily limited environment with poor layout and little in the way of testing. The JKP tool offers a major improvement but is still not closely integrated with the normal spreadsheet formula development process.
In general use, spreadsheets can contain errors within the definition of Names which go undetected simply because the reviewer is unwilling to open Name Manager and will, instead, devote all their energy to tracking president trees to find errors in the direct references.
2. Code pane. Again, detached from normal spreadsheet formula writing. I believe it has a place, but only if it becomes the new formula bar and is used to develop all formulae, whether they are then assigned to a defined Name or a worksheet cell (maybe validation and conditional formatting as well).
3. Data table. I have used 'home knitted' versions of this, both for Lambda functions and regular Names. In each case VBA was used to upload the selected Name to the Workbook Names collection. In each case, it worked far better than Name Manager, but was idiosyncratic and fragile.
4. DEFINE function. I have great hopes of this. It stems from the observation that the LET function inherently applies local names to functions. It is also LET that acts as a precursor to LAMBDA and, in doing so, provides the basis for seismic change in the normal spreadsheet solution development. process. I expect I will amplify this point sometime, but not now.
My belief is that it is a combination of 2 and 4 that offers the best possibilities. It would mean that all formulas are visible from the worksheet, whether they are evaluated in place, within the cell, or assigned to a defined Name and evaluated as functions within other formulae. As future formula development includes the use multiple statements to define the functions that then underpin the solution, the formula bar needs to be replaced to support such development. Ideas such as that put forward by tboulden which allow the user to step through a LET function, evaluating each local name in turn would also be of immense value.
PeterBartholomew1 lori_m Thanks for this new thread! I've not got much to contribute at this stage other than my agreement with the general sentiments expressed. I'm set to explore and play with the data-validation/VBA loading implementation and will come back with my experience notes, and revisit my early experiments debugging.
One thing I thought might be useful to bring up since we're dealing with a functional approach is the concept of lenses. I've only read about them as a non-expert, but my understanding is that they allow for inspecting parts of data structures in a functional-friendly way. I believe there could be a GET lens to inspect name/value pairs either as local variables in LET or LAMBDA; similarly a SET or PUT lens might have a way of updating formula/definitions/etc from a functional programming perspective. I don't have any specific reference to share, but I did see that some implementation exists in TypeScript, so atleast it doesn't seem entirely outside the realm of possibility since I believe Calc.ts team revamped formulas/functions to behave with Excel on the web in TypeScript, and part of that is what's given us LAMBDA.
lori_m Thanks for the link to Excel Virtually Global, I'm particularly intrigued by Charles Williams mysterious "lambda variables" title, but there are sure to be many useful tidbits.