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.
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.
lori_m PeterBartholomew1 I wasn't able to fully hash out why I was getting the two entries, but I worked around it by deleting the Workbook scope entries manually.
Some highlights:
- defineIt, a wrapper for DEFINE that hard codes everything but comment and scope
- nameDtl, a wrapper for DEFINITIONS that provides some dynamic filtering
- pseudo-Sheet Defined Function (SimplePivot tab): everything was defined using defineIt with local scope and step-by-step detail for building the pivot function is presented.
- pivot, a rudimentary pivot table LAMBDA function
- summarize, built to extract a subprocess of the original pivot algorithm shown on SimplePivot, and to resemble DAX function of same name
- fair amount of uses of thunks
- aggHelper can take custom LAMBDAs, so you can actually get much more specific aggregations that would require a data model and DAX
- some other helper functions
Need to polish this a bit, but its all in working order currently, tell me if you notice something I missed or is not clear.
Thanks for looking into this. I think the scope parameter is designed to input either sheet name or workbook so one can specify any sheet scope. I can't fully test but i was hoping something like this would work
=LET(√,LAMBDA(x,SQRT(x)),DEFINE("Sheet1",TRUE,"function","LAMBDA","Square Root"))
In my version the scope is shown as 'Sheet1' and it looks like a function category is created for lambda but i don't see the function listed there yet. I've got other commitments at present, but plan to come back to this in a month or so.. Any help with bug fixes / improvements much appreciated in the meantime.
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 IfThis 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.
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.