Forum Discussion
Is it time to revisit the Defined Name? I believe 365 requires a renaissance of the Name.
From a recent LinkedIn discussion it became evident that the adoption of Named Ranges for referencing data (although reasonably well known) is far from universal.
If you don't think naming is important now, try working with Excel 365. At the moment, those Names that are used, typically define Ranges that are referenced from VBA or are widely referenced from other sheets.
From my recent experience working with 365, I would say the majority of Names no longer represent ranges at all, they are the intermediate results of calculations performed within LET functions that extend over many lines.
Move to insider beta and that changes again. The most important Names will not apply to data but rather Lambda functions representing the formulas that are used to process the data.
After experimenting for a while with LET and now LAMBDA, I have come to think that the change of practice and mindset required to create good 365 solutions is so great that one's past experience of spreadsheet development may itself be the greatest impediment to one's future progress!
This discussion is intended to follow on from
which explored some strengths and limitations of 365 dynamic arrays (some remarkable material provided by tboulden and lori_m). Eventually, it reaches a point at which a issue is raised which could be summarised by "Since the majority of names will be introduced by LET and LAMBDA as local names, is it so unreasonable to expect all names to be declared within worksheet formulas rather than hiding them away within Name Manager?
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.
21 Replies
I defined a possible syntax in which DEFINE (or maybe a pair of functions, GLOBAL and LOCAL, replaced the keyword LET in functions with similar syntax. I speculated that it might be possible to mock up the functionality, using a Lambda function to turn DEFINE into a legitimate LET formula and then using VBA to add the Name to the Sheet or Workbook Names Collection.
lori_m came up with a remarkable VBA module that I am only just beginning to explore. So far I have taken a dataset from the earlier discussion and used the new functionality to define some key Named Formulas essential to the analysis. I then also successfully uploaded a Lambda function (a mundane SQRT function named "√"). Not Earth-shattering I know, but I needed to start somewhere.
The question for discussion is "What do you think of the idea of defining Names (formula-local, sheet-local and workbook-global) on the worksheet where they are visible for review and audit?" I appreciate that, for many, it is too early to say, but I would love to hear your views as 365 becomes more widely available.
Thanks for preparing this follow up, I was actually putting together a possible listing of name manager replacements to post as a new thread but you beat me to it! The list so far includes:
1. Name Manager: An enhanced Dialog perhaps similar to that of MVPS Charles Williams and JKPieterse
(http://dailydoseofexcel.com/archives/2020/12/14/name-manager-updated-2 )
2. Code Pane: The Lambda Editor / Playground released by MSR has similarities with Office Scripts Editor and would likely suit the developer community well. I gather Andy Gordon and Jack Williams will be presenting at https://www.sumproduct.com/news/article/news/excel-virtually-global-2021-coming-soon
3. Data Table: An editable table like the one generated by the paste names command which updates on refresh, perhaps with a link to the data model or xml source data. I don't have enough background to assess the pros and cons of this kind of approach.
4. Define Function: A worksheet function that updates names on formula entry based on the previous prototype. I am currently testing a worksheet function that wraps a LAMBDA or LET function effectively converting local names to workbook names, I hope to post soon!
My feeling is that some combination of these proposals is needed to cater for different use cases. I'd be interested to hear others thoughts...
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.
