SOLVED

Is it time to revisit the Defined Name? I believe 365 requires a renaissance of the Name.

Silver Contributor

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

A statement of truth or a wasted opportunity. Lambda helper functions reluctant to return 2D arrays....

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?

 

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.

@Peter Bartholomew 

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 @Jan Karel Pieterse 

(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...

@lori_m 

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.

@Peter Bartholomew @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.

@Peter Bartholomew 

I believe next iteration of the editor includes 2) and 4) in some form, that's the logical extension to marry editor with names manager. As for me I'd prefer this, preferably platform independent, variant.

@Sergei Baklan 

To move beyond Windows desktop to Mac/Web/Mobile would require ditching VBA, which I'm not a big fan of anyway, and moving to TypeScript (with lenses?) or XLL - both sound a bit daunting!

 

@tboulden @Peter Bartholomew 

I aim to release an update of the previous code in the next few days - I was initially intrigued by the idea but can't afford to spend too much time on VBA code at present. I also thought of another potential candidate for a name manager replacement while working through the DEFINE concept:

 

5. Linked Data Types. A Defined Name data type with properties to include Name, RefersTo, Scope, etc. Ideally the RefersTo property for the name could be updated from a formula in the cell, currently one can use FIELDVALUE for reading but not writing field properties. Not sure how that would tie in with existing structure, but could maybe provide a more integrated user experience?

I like this idea also; I experimented a bit with creating a data type earlier this year, but it wasn't particularly user-friendly since its creating a table, loading to Power Query, creating the data-type, loading the data-type table back to the workbook, then referencing the data-type, which only gives back the text, but not an active formula, so you have to copy-paste-special, then modify, etc. I had thought originally since LAMBDAs were TYPE=128 and data types were as well that it would be apparent how to make them work together in a nice way.

@lori_m 

Yes, in general it could be Type Script. But for this concrete case it shall be out of the box formula (not only lambda) editor. Kind of mini-IDE.

@Sergei Baklan 

The VBA was not seen as a permanent element of a software system, merely a way of creating a concept demonstrator.  Not that it's much of a limitation for me, because it happens that I only use Windows desktop machines.  I was very interested in your assertion that Microsoft are likely to trial an interface that combines the side pane code window (2) and worksheet functions, such as we are proposing i.e. DEFINE (4).  I was not aware of any prior work aimed at using worksheet formulas in place of a Name Manager but I am quite prepared to accept that we may well be 'reinventing the wheel'.  Thank you for bringing it to our attention. 

 

 @lori_m's code has allowed me to create global named formulae in much the same way as LET creates names local to the formula.  The last thing I tried with the previously posted workbook was to use it to define a Lambda function.  The existing formula parsing does not allow me to introduce dummy data to test the function while it is being developed, but the correct Lambda function is uploaded, even though a #CALC! error is displayed as the result of the definition.

 

I hope that, by using the emulator, I can firm up my ideas concerning the desirability of moving the management of Named formulas to the worksheet.  My provisional view is that it will help the auditability of the workbook to display the overall flow of the calculation on the worksheet, without needing to dive into 'semi-hidden' utilities.  Ultimately, the increased prominence of defined names (especially as Lambda functions) will mean that the protection of Names becomes even more important than the protection of cells, but that is for the future.

 

@lori_m To bring rich data types seamlessly into the fold would be impressive, but right now I am happy to climb one mountain at a time.

@Peter Bartholomew 

Maybe try adding the line below to prevent the dummy data affecting the parsing?

[...]
Case ")"
If Not inQuotes And Not inDQuotes Then parens = parens - 1
If parens = 0 Then fmla = Left(fmla, i - 1): Exit For
[...]

I'm not able to edit the file due to office version but I'll be posting an xlam addin soon which should allow for additional variables. Also note the parameters passed to the validation call can be checked by entering in an unused area =args(cellref).

@tboulden 

That was in the back of my mind too, since lambdas and linked data types appear to share the same type signature one might expect they could be combined in interesting ways. I'll be interested to hear what is said at the conf next week...

@lori_m @Peter Bartholomew Andy Gordon and Jack Williams from Microsoft Research revealed that the LAMBDA editor add-in they've been working on should be available via the Microsoft Store sometime in the near future. Video should be available here, under the "ACCESS" section, first session, around hour 10 of the Teams recording.  What they demo'ed today has syncing with GitHub, which would be helpful in managing LAMBDA versioning, but overall it looks like there are some nice improvements!

best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@tboulden @Peter Bartholomew 

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.

lori_m_0-1634316679836.png

 

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.

Excellent! I've incorporated the add-in and done some sample experiments, and glanced through the VBA; this seems quite nice, thanks for all your effort on this!! Will provide feedback and try to make myself a bit useful on this front!

@tboulden 

Great, glad you managed to get it working. On my setup I just noticed that I need to uncheck and recheck the addin to register the two DEFINE and DEFINITIONS functions after launching Excel, this can be done from the developer tab as below. I'll try and add an update for that next week. Please let me know of any other issues...

 

I wanted to also check if LAMBDA work as expected like in the xlsx attachment.

 

lori_m_0-1634380753091.png

 

@lori_m 

That looks truly amazing.  Now I have to learn how to use GitHub!  It also seems to be at the centre of the MSR approach to sharing Lambdas so I guess it will be time well spent. 

 

From their presentation, it also appears that the Lambda editor has moved on since the time I downloaded it.  The reservations about the Lambda editor that I still have is that it takes one away from the spreadsheet and into a code environment for development and the auditor is forced to follow.

 

I think I would prefer it if Jack's Lambda editor were primarily a replacement for the Formula Bar rather than Name Manager.  The idea is then that the scope of the name is controlled by the LET formula so everything is visible and auditable from the spreadsheet.

@Peter Bartholomew 

Combining name definitions with a floating/dockable formula bar would seem ideal. A couple of related possibilities that come to mind are updating named formulas via
- the 'Name Box' next to a formula (instead of using it to define a range name)
- a 'Names Pane' similar to Selection Pane (alt+f10) which is editable from the formula bar

In effect a name could become a valid selection distinct from any cell reference having its own formula. I do have doubts, however, whether this kind of thing could be implemented as a third party solution due to the lack of extensibility around the formula bar.

 

On the other hand, the LET approach does have some advantages by keeping everything in one location on the worksheet, allowing multiple names to be updated at once and also being able to incorporate testing and updating into a single action. Due to the validation, I find the names list may sometimes need to be manually refreshed using F9, @Charles Williams has a useful tip too - clicking away rather than hitting enter after a formula update changes recalculation order with data validation (http://www.decisionmodels.com/calcsecretsc.htm)

 

RE GitHub: these steps seemed to work ok for me.
1. From the previous Github link click download and copy the file (ctrl+C).
2. Go to Developer Tab > Excel Add-ins then click browse and paste the xlam file (ctrl+V)
3. Click to add the file as an add-in (always check code before enabling macros!)

4. Excel Options > Trust Center > Trusted Locations > Add the Add-ins folder location

It turned out the issues i was experiencing before were due to not having done #4.

@lori_m 

Sorry I have been somewhat unresponsive. My Surface dock decided it no longer wanted to talk to the monitors and I am not happy at the prospect of trying to chair conference sessions with just a laptop screen. I think it is software repair/update that is required because another machine still drives the setup correctly.

Back to Excel, I have downloaded the Add-in and will try it as soon as I get sorted.  On the Lambda front, both the formula bar and name manager fall woefully short of requirements, so I would like to see something based upon Jack's editor in the role of "the Excel formula editor" and ultimately have it apply to worksheet formulas, defined names, conditional formats, validation formulas and the chart engine (is that all?).  Actually the last three are probably best generated through the use of defined Names even now.

@lori_m  So far, so good; I made one successful modification to the Parse function to accomodate {,} for arrays as names. I'm struggling a bit with my other modification. I couldn't get the scope variable to register anything but "Workbook", so I Dim'ed a var ws for Worksheet and modified setNames to this:

        If IsEmpty(A(eScope, i)) Or A(eScope, i) = "Workbook" Then
            Set o = wbk
        ElseIf A(eScope, i) = "Local" Then
            Set o = ws
        End If
        

 

This is working, but it is adding two entries: 1 with the local sheet scope and 1 with "Workbook" scope. I've tried figuring out where its doubling up, but not having success. Any ideas? Once I get this working, I've got a LAMBDA-based pivot table function I'll showcase here as well.

1 best response

Accepted Solutions
best response confirmed by Peter Bartholomew (Silver Contributor)
Solution

@tboulden @Peter Bartholomew 

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.

lori_m_0-1634316679836.png

 

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.

View solution in original post