Announcing LAMBDA: Turn Excel formulas into custom functions

Published Dec 03 2020 08:00 AM 353K Views

LAMBDAfunction_Concept_USEME.png

Today we are releasing to our Beta customers a new capability that will revolutionize how you build formulas in Excel. Excel formulas are the world’s most widely used programming language, yet one of the more basic principles in programming has been missing, and that is the ability to use the formula language to define your own re-usable functions.

 

=LAMBDA

Simply put, LAMBDA allows you to define your own custom functions using Excel’s formula language. Excel already allows you to define custom functions, but only by writing them in an entirely different language such as JavaScript.  In contrast, LAMBDA allows you to define a custom function in Excel’s own formula language.   Moreover, one function can call another, so there is no limit to the power you can deploy with a single function call. For folks with a computer science background, you’re probably already familiar with the concept of lambdas, and the introduction of LAMBDA makes the Excel formula language Turing Complete...

 

Reusable Custom Functions

With LAMBDA, you can take any formula you’ve built in Excel and wrap it up in a LAMBDA function and give it a name (like “MYFUNCTION”). Then anywhere in your sheet, you can refer to MYFUNCTION, re-using that custom function throughout your sheet. I’ll show a couple examples below.

 

Recursion 

Reusable functions is reason enough to start taking advantage of LAMBDA, but there’s one more thing… you can do recursion. If you create a LAMBDA called MYFUNCTION for example, you can call MYFUNCTION within the definition of MYFUNCTION. This is something that before, was only possible in Excel through script (like VBA/JavaScript). I’ll also show an example below of how you can leverage this to build things that were not possible before without writing script.

 

Reusable Custom Functions

One of the more challenging parts of working with formulas in Excel is that you often get fairly complex formulas that are re-used numerous times through the sheet (often by just copy/pasting). This can make it hard for others to read and understand what’s going on, put you more at risk of errors, and make it hard to find and fix the errors. With LAMBDA, you have re-use and composability. Create libraries for any pieces of logic you plan to use multiple times. It offers convenience and reduces the risk of errors.

 
Station IDs

For example, imagine I have a list of station IDs, where the state is encoded in the ID, and I want to pull that value out:

 

StationID_1.png

 

There are many ways to do this with Excel functions, here’s how I did it (I’m sure many of you have much more efficient ways to doing this… forgive me… I know for example the team would remind me I should take advantage of the LET function)

 

=LEFT(RIGHT(B18,LEN(B18)-FIND("-",B18)),FIND("-",RIGHT(B18,LEN(B18)-FIND("-",B18)))-1)

If I take that formula and copy it down the column, I can get the results shown in the table above.

 

There are two challenges with this approach:

  • Errors - If I find an error in my logic that I need to fix, I have to go back and update it everywhere it was used, and I might miss some. Additionally, there’s added risk whenever you have complex formulas repeated over & over again as opposed to defined just once and then referenced. If for example, there are some station IDs that look like this “105532-872332-WA-73”, my formula would not work with those. If I find this error and want to fix it, I then need to go back to every cell where I used that logic and update it.
  • Composability/Readability - If I’m not the original author, it’s hard to know what the intention of that formula is (to pull out the location). It’s also hard to use this logic in combination with other logic, like if I want to take the station ID and do a lookup based on the calculated location.

Using LAMBDA, I can create a function named GETLOCATION, and put the formula logic in the definition for that function.

 

=GETLOCATION

 

=LAMBDA(stationID, LEFT(RIGHT(stationID,LEN(stationID)-FIND("-",stationID)),FIND("-",RIGHT(stationID,LEN(stationID)-FIND("-",stationID)))-1))

Notice I specify the arguments my function will take (in this case stationID) and the logic for my function. Now in my spreadsheet, I can simply write GETLOCATION as a formula and reference the cell that has the stationID, just like any other Excel function. If I notice I have an error, I fix it in one place, and everywhere that uses that function is fixed.

 

STATIONID_GIF.gif

 

Another added benefit, is that I can now compose that function with additional logic. For example, if I had a table of tax rates for each location, I could write this simple formula to return the rate based on the stationID.

 

=XLOOKUP(GETLOCATION(B18), table1[locations], table1[tax]).

 

OK, lot’s more to drill into here around how you can use this capability to build up a rich set of function libraries, make your sheets easier to understand and less error prone, etc. These functions can even take data types as arguments. We’ll post an example later of a custom function that takes two cities as input and calculates the distance between them using the geo coordinates and the radius of the earth to perform the calculation.

 

Let’s shift into the other big impact this will have on what you can build in Excel. This one is a bit more complex, but is pretty revolutionary for us… recursion.

 

Recursion

One of the big missing pieces in Excel formulas has been the ability to loop… to repeat over a set of logic at a dynamically defined interval. There are ways that you can manually configure the interval at which Excel recalculates to mimic this to an extent, but it’s not inherent to the formula language. That changes with LAMBDA.

 

Let’s take an example, albeit a bit contrived, but it’s a simple way to get the point across.

Imagine I have a set of strings, and I want to specify which characters should be removed from those strings dynamically:

 

StringClean_1.png

 

Because the set of characters you’re specifying are not static, there really isn’t any good way of doing this. If you knew it was always a fixed set of characters, you could do a ton of nested logic, but that would be pretty complex and error prone to author. Not to mention, if the number of characters to be removed was larger than what you’d accounted for, it would fail.

 

With LAMBDA, we can create a function called REPLACECHARS that references itself allowing you to iterate over the list of characters to be removed:

  

=REPLACECHARS

=LAMBDA(textString, illegalChars,
     IF(illegalChars="", textstring,
       REPLACECHARS(
       SUBSTITUTE(textString, LEFT(illegalChars, 1), ""),
       RIGHT(illegalChars, LEN(illegalChars)-1)
)))

Notice that in the definition of REPLACECHARS, there is a reference to REPLACECHARS. The IF statement says if there are no more illegal characters, return the input textString, and otherwise remove each occurrence of the leftmost character in illegalChars. Recursion kicks in with the request to call REPLACECHARS again with the updated string, and the rest of illegalChars. This means it will keep calling itself until it has parsed over every character to be removed, giving the desired result.

 

REPLACECHARS_GIF2.gif

 

Not just numbers & strings

If you’ve been following the Excel improvements over the past couple years, you probably noticed two significant improvements with the type of data you can work with in Excel:

  • Dynamic arrays - Rather than passing a single value into a function, you can pass an array of values, and functions can also return arrays of values. You can learn more about arrays here.
  • Data Types – The value stored in a cell is no longer just a string or a number. A single cell can contain a rich data type, with a large set of properties. You can learn more about data types here.

Functions can take data types and arrays as arguments, and they can also return results as data types and arrays. The same is true with the lambdas you build.

 

Let’s take an example... I have a list of cities, and I want to calculate the total distance I’d travel if I were to go to each city in order.

 

We’ll post the code for this one in a later post, but it’s pretty basic:

  • We have an array of City data types. The city data type has the latitude & longitude properties.
  • With latitude & longitude, we can do some basic math using the radius of the earth to approximate the distance between two points (that’s the first Lambda we call DistanceBetweenCities)
  • We create a recursive lambda, DistanceBetweenMultipleCities, to iterate over the cities in the array. In addition to calling itself, to iterate over the list of cities, it also calls the DistanceBetweenCities function to get a running total of the distance traveled.
DISTANCEBETWEENTWOCITIES_GIF2.gif

 

Try it yourself

If you are in our Beta program, you can try it now yourself. We’d love your feedback as we continue to improve on this new capability.

 

Note: The lambda function is available to members of the Insiders: Beta program running Windows and Mac builds of Excel. Learn more about how to become an Insider here

 

LAMBDA Overview

There are three key pieces of =LAMBDA to understand:

  1. LAMBDA function components
  2. Naming a lambda
  3. Calling a lambda function

 

LAMBDA function components

Let’s look at an example which creates a basic lambda function.

 

Suppose we have the following formula:

=LAMBDA(x, x+122)

In this, x is the argument you can pass in when calling the LAMBDA, and x+122 is the logic.

 

For example, suppose you called the lambda and input the value 1 for x, Excel would do the following calculation:

1 + 122

Which, as we all know:

1 + 122 = 123

But how do you use these? If you've been pasting our examples into Excel, you may have noticed some #CALC! errors. To resolve those, you'll need to learn the next step.

 

Naming a lambda

To give your LAMBDA a name so it can be re-used, you will want to make use of the Name Manager.

 

The Name Manager can be found in the Ribbon by going to:

Formulas > Name Manager

chgross_38-1606849602985.png

 

Once you open the Name Manager you will see the following window

chgross_39-1606849602988.png

 

From this point you’ll want to create a new entry (New…) and fill out the associated fields

chgross_40-1606849602990.png

Name: The Name of your function

Comment: A description and associated tooltip which will be shown when calling your function

Refers to: Your lambda function definition

 

Once you’re done, you can hit “OK” to store your lambda and you should see the definition returned in the resultant window.

chgross_41-1606849602993.png

And that’s it! Now you can make use of your newly crafted custom function in the workbook by calling it by its name. 

 

Calling LAMBDA

To put it simply, you call a lambda function the same way you call native functions in Excel.

 

To illustrate this, lets revisit the previous example and show how to call MYLAMBDA with a value.

=MYLAMBDA(122)

Which of course returns the value:

123

One last thing to note, is that you can call a lambda without naming it. If we hadn’t named the previous formula, and just authored it in the grid, we could call it like this:

=LAMBDA(x, x+122)(1)

This passes in 1 for x, which returns

123

 

Just the beginning... join us for the journey

As you’ve probably noticed, we are improving the product on a regular basis. The desktop version of Excel for Windows & Mac updates monthly, and the web app much more frequently than that. We have a ton of improvements already planned for LAMBDA, but we’re looking to you for feedback along the way. One that I can tell you gets me every time is the experience of editing in the name manager... definitely lots of room for improvement there. We’ll also add some more array manipulation functions in the coming months that will help you build even more powerful lambdas taking more advantage of dynamic arrays.

 

Office Insiders

Join the Office Insider Program and choose the Beta Channel to get early access to LAMBDA in Excel. 

 

To give feedback and suggestions, click Help > Feedback, and Add #LAMBDA in your feedback so that we can easily find input about the feature. You can also post in the Excel Tech Community.

 

Connect with us

Stay connected with us.  Join our Excel Tech Community and check out this LAMBDA sample. Let us know what you think and we’d love to see what you build with LAMBDA!

 

Learn more

To learn more about LAMBDA, please check out our help article and in the meantime we are excited to hear more from you about the LAMBDA formulas you have created!

 

LAMBDA Help

213 Comments
Occasional Contributor

Something wrong:

I read: 

Lambda function is available to users running Insider builds on Windows and Mac.

  • Windows: Beta Channel Version 2012 Build 13519.20000 or higher.
  • Mac: Beta Channel Version 16.45 Build 1201.0 or higher.

I have 13530 but no =LAMBDA (yes I have ot from Insider channel)

What I can do or wait for

Trusted Contributor

@JackIsJack 

I agree.  I wouldn't leave formula development or debugging steps visible to the end user.  They are either deleted, once validated code has been uploaded as a named formula, or hidden.  Then again, my workbooks tend to be very 'app-ified'; if the requirement were to share development with the end user they would have to look and behave somewhat differently. 

 

2048 game version for Excel (spreadsheet1.com)

Statistical Significance Batch-Testing Tool for Multiple Items – The Stats People

World cup 2014 Excel prediction templates | Spreadsheet1

 

You raised an interesting point that the reuse of a named lambda might be very different from its original intent, to the extent that the name may be positively misleading.  I must admit, that is a scenario I hadn't considered.  Amanda had better ensure her latitude and longitude are uploaded at regular intervals if Georges code is to be effective

@Henn Sarv , it covers 50% of insiders. Thus wait.

Trusted Contributor

@Brian Jones 

@Sergei Baklan 

 

I have come across some presentations on 

Elastic Sheet-Defined Functions: Generalising Spreadsheet Functions to Variable-Size Input Arrays - ...

 

Is this the same strand of research that led to the Lambda function? 

If so, somewhere along the line there has been a massive breakthrough in terms of usability and syntax!  The present approach seems to be a perfect match for LET and the array functionality that have simplified Excel solutions so much.

 

I can't wait 'til I come up in the beta-release lottery!

Occasional Visitor

Cool!

 

Waiting for embedded AI next!

@Peter Bartholomew , I know only what is published on Calc Intelligence - Microsoft Research site, not more.

Occasional Visitor

I have been wanting something like this for quite awhile. I've always been using Named Members to conduct complex calculations, but that always required the reference to be the same (i.e. Two rows above). Having the ability to collect an input makes things far easier. Can't wait to try it.

Occasional Visitor

Regarding comments about Excel needing an editor: how about a VS Code Extension which would allow direct, read/write creation, editing and management of the named items found in the Name Manager?

Trusted Contributor

@Jan Karel Pieterse 

 

<I guess Name Manager by Charles Williams and I will have to fill that gap once more>

 

Yes please!  If you could scope the work to allow any named formula to developed (from scratch or by modifying an already defined formula) using some user-specified scratch space on the worksheet, that would be of great value.  Ideally the upload process should accept the cell formula from working formulas or the cell value if the name is defined as a text string.  The characteristic of LAMBDA that makes it different from existing formulas is that the parameters would need to be omitted from the defined name.

 

1) Although it may be expected that many named formulas will now be moved to the LET function with single-cell scope, there is the possibility that the LET function may itself appear within a named formula, greatly increasing the complexity of the defined names one may wish to create.

 

2) I have suggested working within some designated space on a worksheet simply because I assume that will be far easier than developing a form that supports IntelliSense and can display dynamic arrays; the latter may well be impossible

 

Trusted Contributor

It occurs to me that I might have a use for LAMBDA to encapsulate the complexity of looking up multidimensional datasets, which are often held according to weird and wonderful layouts or could require searching database tables by key.

 

The nice thing about such a function

= UnitSales(Product, Region, Sector, Month)

is that the format coincides with the conventional notation for returning an element of a multidimensional array.

There was a requirement to sum over sectors, which could be handled by explicit summation or, I think, by defining a further recursive function. 

 

UnitSales: = LAMBDA( Pr, Rg, Sect, Mnth, INDEX(data, ...

Occasional Visitor

What I'd like to know is why did it take so long to do this? It's been an obvious hole in Excel since version 1. Who writes a computer language with no function definitions, no recursion, no iteration, and no data types? 

Occasional Visitor

cell formula iteration ie recursion is an option you can turn on: File > Options > Formulas > Enable iterative calculation

 

also an article about lambda functions should probably have a lambda example.

Frequent Visitor

It is unwise to use Name as the entry  of Lambda user-defined function. The Name means that it cannot be globally effective for Excel. If there are commonly used custom functions, you have to add a Name to each Excel document to use it. It really drives the user crazy. It is suggested that it can be used globally just like Excel Custom List, which is the real customization.

Occasional Visitor

Does Name Manager even exist in Excel Mac? - I  can only find define name and that refers to ranges only.

 

stevegroom_0-1608054902083.png

 

 

@stevegroom , I'm not on the Mac, but I guess you may insert formula instead of reference. Approximately the same box is on Windows

image.png

Trusted Contributor

@stevegroom Have you tried to follow @Sergei Baklan  suggestion?

e.g. name a cell to be 'x'.  Define a new name 'y' to be

= x+1

 I tend to regard all names as formulas, some just happen to reference ranges.  If the Mac were different, surely that would cripple any semblance of platform independence. 

Frequent Visitor

Same as Henn here. I have O365 & part of Insider Program.

Excel > Account shows: Version 2012 (Build 13530.20144 Click-to-Run), Current Channel (Preview) 

But =LAMBDA is not recognized.  Any idea?

 

Great feature though! Eager to play with it.

Occasional Visitor

Great feature, thanks, team!

 

Any plans to include this in the mobile (iOS and Android) versions of Excel (if feasible, I'm not sure what related features might be missing in the mobile version, but this sure would make Excel the star app on my phone)?

 

Occasional Contributor

I have Office for Enterprice (this is O365 subscrition)
and 2021 january version
after anounced =LAMBDA I've received 2 new updates

but NO LAMBDA

 

HennSarv_3-1608367304266.png

 

 

 

 

 

 

 

@HennSarv , updates now don't matter, that's only wait while lambda functionality will be switched on for your account, other words when 100% of insiders will be covered.

@Coool1710 , I have lambda on Excel desktop, but not on on Excel Online, not sure if it is implemented on this platform for now.

Occasional Visitor

Excellent Thank you.

Honored Contributor

I find this extremely useful, it just makes so much sense to have this feature in Excel.

Occasional Contributor

Still waiting to be 'covered insider'

Nice to read about how usefull, but can't try

 

Is there ANY way to enforce this or might be I forget something to do to switch on this

 

WANT LAMBDA!

 

Henn

@HennSarv , to my knowledge there is no way to enforce, only to wait. Or to try with another few installations on VM:s or/and under different accounts, perhaps you'll win a lottery.

Senior Member

I'm registered for the "Insider Program", however, my version of Excel does not appear to have the ability to use or create Lambdas (Version 16.46 (20123000)). Once again I assume this new function is only for Office 365 Widows not Office 365 Mac?

Occasional Contributor

I have been working extensively with #Lambda and it is indeed great! While it enables me to add extremely interesting functions, it does not allow me to lock it.

 

I am foreseeing a situation where an organisation may want to create a library of functions in their base excel template. But if an organisation were to rely on these Lambda functions, then we should be able to prevent unauthorised changes to them.

Therefore, it would be great if the team can work on a feature to lock names with password (without having to protect the sheets or the workbook).

If you find merit in the point, I request you to please upvote this uservoice suggestion. @Wyn Hopkins  @Peter Bartholomew @Craig Hatmaker 

 

Create Name protection feature to prevent unauthorised change to Lambda functions – Customer Feedbac...

Trusted Contributor

@HennSarv 

cc @Brian Jones , @Sergei Baklan 

I wonder whether there is a way to convert our discontent into a formal customer service complaint?  I wouldn't wish to sour the tone of this blog which is announcing a hugely important innovation which addresses the most significant limitations of dynamic arrays (1D aggregations of 2D arrays, accumulation / corkscrews and the management of multidimensional arrays for a start).

 

I fully appreciate that Microsoft needs to manage its rollout of new functionality to control risk and to constrain feedback to a level where is may be actioned.  That said, to be unable to respond to users who have a particular interest or need to deploy the new functionality is unfortunate.  Generating resentment within the very groups that have the greatest commitment to supporting MS Excel (I believe this includes MVPs) must be counterproductive. 

Occasional Contributor

On request I hope several people can agree is to extend Defined name scope.

Currently there is only 2 options - workbook scoop and worksheet scope. Time to extend and add something like global scope. This allows for example create custom named global functions available in all wrokbooks. (need to be oaded as XSTART or XLADDIN

 

Occasional Contributor

One exaple of useful global function (better, when this will be created as regular excel function)

UNION:=LAMBDA(
array_a,array_b,
   LET(
      size_a,rows(array_a),
      size_b,rows(array_b),
      size,size_a+size_b,
      cols,sequence(,min(columns(array_a),columns(array_b))),
      seq,sequence(size),
      if(seq>size_a,
         index(array_b,seq-size_a,cols),
         index(array_a,seq,cols)
      )
)

PS! very useful will be #/ comment /# like feature in excel formulas + indentation

Frequent Visitor

Lambda is available today. My version is 13628.20000.

@ExcelVIP , thanks for sharing, hope another wave of deployment is started

Occasional Contributor

@ExcelVIP ; @Sergei Baklan 

 

Thanks - =LAMBDA is there and works

 

Occasional Contributor

GREAT! 

My function works (only one little typo - missing bracket)

HennSarv_0-1609870928740.png

 

Occasional Contributor

I ask - some of You might know

 

may I use anouncmenet logo picture as illustration on my blog post? I just created one and used.

(so I ask afterward)

One post in FB referencing my BLOG and post in my BLOG sarviktaat.wordpress.com

 

Occasional Contributor

One positive behaving - workaround global λ-functions

 

Copy any sheet from one Excel Book into other allso copies all workbook-scoped names 
so - I can have a function library doument and easely copy all my λ-function from this to any other

only - change management will be hard to organize

Frequent Visitor

WOW ! That's a big step in excel formulas

Occasional Contributor

Today I converted some of my old 'functions' into lambda. One example shows how Lambda and Let can by used in each other.
Example is unfortunately in Estonian but realize one till today wanted function - Convert numbers into text

=LAMBDA(arv;
LET(a_miljard;int(arv / 10^9);
    a_miljon;int(mod(arv;10^9) / 10^6);
    a_tuhat;int(mod(arv;10^6) / 1000);
    a_üks;mod(arv;1000);
    a_sent;mod(round(arv*100;0);100);
    f_kolm;LAMBDA(arv_3;
        LET(
            sajad;int(arv_3/100);
            kümned;int(mod(arv_3;100)/10);
            ühed;mod(arv_3;10);
            teised;mod(arv_3;20);
            sõnad;{"";"üks";"kaks";"kolm";"neli";"viis";"kuus";"seitse";"kaheksa";"üheksa";"kümme";"üksteist";"kaksteist";"kolmteist";"neliteist";"viisteist";"kuusteist";"seitseteist";"kaheksateist";"üheksateist"};
            t_sajad;index(sõnad;sajad+1);
            t_kümned;index(sõnad;kümned+1);
            t_ühed;index(sõnad;ühed+1);
            t_teised;index(sõnad;teised+1);
            trim(
                if(sajad=0;"";t_sajad&"sada ")&
                if(kümned>1;t_kümned&"kümmend "&if(ühed=0;"";t_ühed);t_teised)
            ))
	);
   trim(
        if(a_miljard=0;"";f_kolm(a_miljard)&if(a_miljard=1;" miljard ";" miljardit "))&
        if(a_miljon=0;"";f_kolm(a_miljon)&if(a_miljon=1;" miljon ";" miljonit "))&
        if(a_tuhat=0;"";f_kolm(a_tuhat)&" tuhat ")&
        if(a_üks=0;if(arv=0;"null";"");f_kolm(a_üks))&
        if(arv=1;" euro ";" eurot ")&
        if(a_sent=0;"00";f_kolm(a_sent))&if(a_sent=1;" sent";" senti")
        )
))

Here You see something like INTERNAL function defined and used inside LET.

Again - only I need is some kind of way to add comments and some better desiner - cell edit is a bit better but name definition is terrible
I used 'integrated IDE named notepad  

Trusted Contributor

Happiness.  LAMBDA has arrived!  OK, so I accept that is a somewhat geeky viewpoint.

I understand that the function is now released to 100% of beta channel users.

 

Just in case it helps, I wrote the following macro to load Names from the worksheet.

Sub CreateName()
Dim NameString As String
Dim RefersTo As String
Dim selectedNames
Dim cell As Range

If Not Intersect([defined.names], Selection) Is Nothing Then
    Set selectedNames = Intersect([defined.names], Selection)
    For Each cell In selectedNames
        NameString = cell.Value
        On Error Resume Next
        ActiveSheet.Names(NameString).Delete
        On Error GoTo 0
    Next
    For Each cell In selectedNames
        NameString = cell.Value
        RefersTo = cell.Offset(0, 1).Formula
        On Error GoTo fail
        ActiveSheet.Names.Add NameString, RefersTo
        MsgBox "Name " & NameString & " set to refer to: " & RefersTo
    Next
End If
Exit Sub

fail: MsgBox "Failed to set " & NameString
End Sub

The Names are held in the range 'defined.names' and the formula is held in the cell to the right, either as a working formula or using the apostrophe to reduce it to a string.  The macro is run by selecting the names to upload and using the 'onAction' property (assign macro) of a shape to run it.  The formula is better for development and the string form for reference.

 

My VBA skills are not the greatest, so feel free to improve the macro if you so choose.

Trusted Contributor

Still playing.

The Lambda function

= LAMBDA(p,q,
  LET( k, n*(p-1) + q,
  INDEX(ArrayStorage,k)))

reads a term from a 2D Array that is stored as a 1D column.  Naming the function 'Array' means that the standard form

=Array(5,3)

returns the appropriate element of the array.  Moreover, calling the function with row and column index arrays k₁, k₂ means that

 = Array(k₁, k₂)

returns the array in its 2D format, and the product

= MMULT(TRANSPOSE(Array(k₁,k₂)), Array(k₁,k₂))

is a positive definite symmetric matrix.  All quite readable!

 

New Contributor

Using Lambda & Recursive Lambda to create user defined custom function Excel
https://www.youtube.com/watch?v=JS8312yLfTE

Examples Covered
1 Square of a number
2 Sum of squares from 1 to N
3 Sum of digits

Discussion Link
https://bi-analytics.org/blogs/entry/54-use-of-lambda-recursive-lambda-to-create-user-defined-custom...

@Peter Bartholomew , congratulations, hope now you are happy!  Didn't test your script, but does it give another result compare to Formulas->Use in Formula->Paste Names?

IMHO, Names is the last resort, better to edit and keep lambdas in cells pasting into the names final result.

Trusted Contributor

@Sergei Baklan 

<hope now you are happy>

Yes.  Yesterday was good.  Received LAMBDA and a shot of the Pfizer vaccine!

 

<Didn't test your script, but does it give another result compare to Formulas->Use in Formula->Paste Names?>

I hope not!  The script was one I had used before, for uploading Named Formulas (which were complex by the standards of the day but nothing compared with the solutions that are now emerging).  It is only intended as a starting point.  For the LAMBDA function it would probably be a good idea to allow function parameters in the cell but strip them out when uploading to the defined name.

 

<Names is the last resort>

I think we are in agreement.  Name Manager does not provide a development environment; that even goes for the JKP version.  I normally develop formulas on the worksheet and then upload (the process has become easier since the plague of implicit intersection has been dispensed with in the grid). 

 

Maybe the community should specify an ideal development environment for Excel formulas; the formula bar is also no longer up to the job.

In my mind it would be a floating window, linked to a specific cell to cover the rare occasions in which relative referencing is needed.  It must support formulas written over multiple lines and using normal indentation conventions.  IntelliSense and the use of Tab for name completion must be available.  The F3 name selection should be usable (possibly continuously displayed).  It should also be possible to use formula evaluation to help identify defects within the formulation.  LAMBDA and recursive formulas provide a new challenge in that regard but that may have to wait a while.

 

Are my ideas too limited?  Would a common look and feel with other MS SDK environments (or PQ advanced editor) be of value? 

@Peter Bartholomew , JKP is great in VBA (and not only), but that solution is not for me. I practically didn't touch VBA for about 10 years and prefer to avoid it, no actual need due to specific of my real projects. I believe lambda management, formula editor and other improvements are coming. But most probably it will be in sync with Excel Online, thus not very fast. That's only my guess, didn't see any related info published.

Occasional Contributor

The formula bar is today quite normal for editing lambdas but some concerns - looks like floating EditFormula window might be nice to have or so called Advance Editor

COncerns:
* hard to add and retain Alt-Enters (LF)
* lack of TAB-support (indent)
* lack of comments - block comments are OK. simply to agree comment 'brackets'

And most concerning:
Error checking (synatax error) is some time very disruptive

=LAMBDA in cell without argument values gives #CALC! error

 

@HennSarv , can't agree that "formula bar is today quite normal for editing lambdas", using of it is quite annoying.

All LAMBDA parameters are optional, you may skip them if only keep the commas, like myLambda(,,,). However, it works if lambda has more than one parameter. Otherwise an error.

@Coool1710 , afraid that's not possible, at least as for today

Contributor

@HennSarv "=LAMBDA in cell without argument values gives #CALC! error"
Yes and also naming that cell ABC and trying =ABC(123) say gives a #REF error.

 

My suggestion would be to be able to just name the cells this way so as to be able to maintain all function definitions on a single sheet without having to copy to name manager.

 

Supporting LAMBDAs as cell references appears to make sense as they return the same type of result as linked data types (128 using TYPE function).

Occasional Contributor

@CooL19  - I didn't say nothing about Your templates? I have similar Q and I have some functions on my add-in (not in templates). Some of them are technically out of scope but some simpler might be convertable. Only who comment Your was @Sergei Baklan , who confirm - at least today.

THere is one function - not convertable (I use very intensivly)

Public Function hlink(r As Range) As String
hlink = r.Hyperlinks(1).Address
End Function

Version history
Last update:
‎Dec 03 2020 12:41 PM
Updated by: