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