Oct 04 2021 02:44 PM - edited Oct 04 2021 02:45 PM
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?
Oct 04 2021 03:06 PM
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.
Oct 05 2021 01:50 AM - edited Oct 05 2021 01:55 AM
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...
Oct 05 2021 03:10 AM
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.
Oct 05 2021 07:49 AM
@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.
Oct 05 2021 09:40 AM
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.
Oct 07 2021 06:22 AM
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!
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?
Oct 07 2021 06:53 AM
Oct 07 2021 10:25 AM
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.
Oct 07 2021 10:37 AM - edited Oct 07 2021 10:39 AM
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.
Oct 07 2021 12:55 PM
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).
Oct 07 2021 01:09 PM
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...
Oct 12 2021 06:53 AM
@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!
Oct 15 2021 10:21 AM
SolutionI 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.
Oct 15 2021 04:03 PM
Oct 16 2021 03:48 AM
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.
Oct 17 2021 01:40 AM
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.
Oct 18 2021 08:28 AM
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.
Oct 18 2021 01:57 PM
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.
Oct 27 2021 11:48 AM
@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.
Oct 15 2021 10:21 AM
SolutionI 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.