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