12 ms·

Lambda: Turn Excel formulas into custom functions

223 points by lelf 6y ago

rajandatta 6y ago
This is extremely significant to simplifying spreadsheets.
- tgv 6y ago
  Yes, but there is no mention of debugging. In the hands of Excel cowboys, this can become another foot gun.
  - bombcar 6y ago
    A number of bugs will be solved by moving to it (a copied function is typo’d in one cell, etc) and you can test it pretty easily in a cell. Excel is basically a REPL with cells.
    - robertlagrant 6y ago
      A REPL treated as a software system.
  - int_19h 6y ago
    Between this and LET (https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...), Excel seems to be a full-fledged pure functional language now, with lexical scoping, first-class and high-order functions etc.
- bombcar 6y ago
  I see it making spreadsheets easier to convert into code, too. Many “business programs” begin as a spreadsheet.
  - lambda_obrien 6y ago
    We have services which run a "notebook as a service" so it's not too far fetched to think of Microsoft doing this with Excel spreadsheets. I bet it would take the business world by storm and would be a good move.
  - robertlagrant 6y ago
    Next up: Excel functions running as Azure Functions in the cloud.
    - avandvrnot 6y ago
      I assumed that was what this is!
dsalzman 6y ago
I hope they add an import and export function to this. Ideally you could sync these functions at an org level via O365.
- ryanmarsh 6y ago
  Then you need version control. Better to let the document be the boundary of consistency.
  - toomuchtodo 6y ago
    Or support versioning using GitHub! Excel has always been a powerful repl, these extensions are leveling it up.
    - geogra4 6y ago
      This would make Excel 100x more maintainable. I would think a nice Github integration would turn excel closer to a maintainable programming language.
      - deleted 6y ago
        [deleted]
ryanmarsh 6y ago
Does Google sheets let you do anything like this?
- cdolan 6y ago
  Yes it supports JavaScript functions that can be called in the formula bar. However Google sheets is nowhere the install base of excel, so this is a really big deal
  - Closi 6y ago
    Excel already has the ability to write custom functions in Javascript (and VBA), this is the ability to write them in the formula language rather than JavaScript. (So no, Google Sheets does not support this) The latest improvements in Excel really do seem to be re-widening the gap between Google Sheets and Excel (Dynamic array formulas, Let, Custom data types, powerquery improvements...)
  - ttul 6y ago
    Is it not, though? Google Sheets is available to all GSuite customers. GSuite is the most popular email hosting platform in the world, with roughly 18% market share (Microsoft 365 trails not significantly far behind). I think Google Sheets has a pretty solid user base.
    - cdolan 6y ago
      GSuite and Office 365 subscription base, while somewhat correlated to, is not equal to the usage of Excel vs Google Sheets. Highly anecdotal, but there are far more complex business processes still running in Excel that are not going to be translated over to Google sheets, and they are all offline behind a network firewall. Those types of sheets will really benefit from this improvement. Now if they just supported python instead of VBA for scripting!
  - cpitman 6y ago
    As already pointed out, these are not the same. Javascript based custom functions in Google Sheets are actually really error prone, meaning that they will just _randomly fail to execute_. Cells will be stuck with a "loading..." message until you trick Sheets into recalculating the value. In the next couple months, I'll likely be ripping out as many custom functions as I can. And these are not for particularly complicated functions. I'd really love to have this feature in Sheets. It would simplify a lot of what my sheets do, and also make it more accessible to a Sheets power user that gets scared off from code.
geocar 6y ago
I love the branding "custom functions without code" right before a bunch of code. Reminds me how early word processors (the person, not the software) were convinced to program word processors (the software this time) just by calling the programs "macros". I'm not joining Microsoft's beta program right now, but I'm curious if anyone knows the data type of a =LAMBDA?
- bitwize 6y ago
  In the 1970s, secretaries wrote their own extensions to Emacs in Lisp. They were only ever told they were "customizing" the editor, not programming it! (This was Multics Emacs, a predecessor to GNU Emacs.)
  - protomyth 6y ago
    "Customizing" is something everyone loves to do in a lot of aspects of our lives. Programming is for those weirdos in IT. Building stacks in HyperCard wasn't programming either it was just writing interactive presentations.
- indymike 6y ago
  Hmm. A lambda platform that just runs spreadsheets. This is a killer idea.
  - fiddlerwoaroof 6y ago
    This is what I thought it was: upload spreadsheet, get API
kevin_thibedeau 6y ago
They sort of already had this with the old style pre-VBA Excel macros.
- JoBrad 6y ago
  This might be better, though. Because it lets you use the same spreadsheet functions, in the spreadsheet. It would be trivial to make a lamdha function that encapsulates several spreadsheet functions into one.
croes 6y ago
Yeah, let's make the moloch even more unmaintainable. Does still have the leap year error to be compatible with Lotus?
- djeiasbsbo 6y ago
  What bothers me greatly about Excel is something that native English speakers perhaps have never had to deal with. The "keywords" are language specific/dependent. Everytime I have to google how to do a specific thing in Excel I then have to spend 5 times longer to translate the instructions into my language. It is a problem because in a locked down corporate environment, a worker/user cannot easily change the language.
  - vic20forever 6y ago
    I think the Functions Translator[1][2] add-in might be worth a look to you. Note: I haven't used it myself, and it's a Microsoft Garage project, so there's no guarantee of support or maintenance. From the description: Functions Translator helps people use a localized version of Excel by helping translate from the US Excel function names, or research how to create a solution on the web with predominately English content. Easily find the equivalent localized functions and formulas in any of the supported 15 languages. Functions Translator will automatically configure the language settings to US and the Localized version, and people can provide feedback on the translation of functions if it is not what they expected. [1] https://www.microsoft.com/en-us/garage/blog/2018/03/new-garage-project-enables-excel-users-to-work-seamlessly-with-functions-and-formulas-across-all-translated-versions-of-excel/ https://www.microsoft.com/en-us/garage/blog/2018/03/new-gara... [2] https://www.microsoft.com/en-us/garage/profiles/functions-translator/ https://www.microsoft.com/en-us/garage/profiles/functions-tr...
- MegaDeKay 6y ago
  This lets you do things like localize a complex formula to one spot that might otherwise be used many times within a workbook. How does that make things more unmaintainable?
DavidPeiffer 6y ago
As a frequent user of Excel, I very much welcome this. Being able to build a function library beyond User Defined (UDF, VBA functions that can be called from a formula) and a Personal.xlsb file (VBA that's available in any open Excel file) One common use case this helps takes the general form "If A2+B2 > 10,then A2+B2, else 10". "A2+B2" needs to be updated, it has to be updated twice. Alternately, you could have a "helper column" C2=A2+B2, but this adds clutter to the whole spreadsheet. I have a number of UDF's which lend themselves well to this, such as a triangular distribution calculator. Execution through Lambda should allow the undo stack to continue working (normally ditched by executing VBA) and hopefully give a performance boost.
- sheetjs 6y ago
  For that type of pattern, you can also use the new LET function: LET(myval, A2+B2, IF(myval>10, myval, 10)) https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...
  - DavidPeiffer 6y ago
    Thanks! Haven't sunk my teeth into the new round of features yet, but have found great utility from the last batch (unique, filter, sort) and the prior batch.
- an_opabinia 6y ago
  Isn’t that just =MAX(A2+B2,10)? Anyway I know what you mean. It seems fine to make a sheet that behaves as variables. Ie a sheet that is two columns, the comment for an intermediate and its values. Reference it elsewhere. Re-engineering the whole Excel as a website, with its attendant sandboxing, seems to be the future.
  - rahimnathwani 6y ago
    This reminds me of the IFERROR(). Without IFERROR, a common pattern would be IF(ISERROR(A2+B2),0,A2+B2). With IFERROR, you can just do IFERROR(A2+B2,0)
ttul 6y ago
The number one reason that I use Google Sheets instead of Excel is the availability of the REGEXMATCH and REGEXEXTRACT functions. No human should forced to use a ridiculous combination of LEFT, RIGHT, and MID to extract things from a string. I just can’t fathom why Excel hasn’t yet introduced regular expressions.
- cdolan 6y ago
  Agreed. I would love to leverage REGEX! Excel formulas have long been too hard to comprehend if you were not the original author... and even if you did author it, 4 weeks later you won’t remember how it worked without a half hour of review!
- chaz6 6y ago
  Whilst not native, you can emulate these with a user defined function (UDF).
- cm2187 6y ago
  Given the number of developers who spit on the floor as soon as they hear “regex” (including me), I am not sure regex will bring much to regular users. If it is hard to developers, it is impossible to regular users. (And if you really want regex, there are hundreds of results on google on how to build your own VBA UDF to get it).
  - aidos 6y ago
    There’s nothing wrong with a small extract or substitute regex, in my opinion.
  - craftinator 6y ago
    > Given the number of developers who spit on the floor as soon as they hear “regex” > If it is hard to developers, it is impossible to regular users. What are you talking about? Regular expressions are used everywhere. On the backend it's text parsing, on the front end it's input validation. I have never written a complete application without using it. You're also the first person I've heard grumble about them. I get that if regex is used in an overly convoluted or messy fashion, they become unreadable and unreliable. Just like assignment operators, nested division, or any other basic programming construct. But they are also remarkably powerful at solving simple pattern matching in a robust way. I recommend you go learn them instead of making baseless claims about "the number of developers who spit on the floor" when talking about them or whatever.
danso 6y ago
This is completely orthogonal to the full scope of LAMBDA, But it did make me laugh to see that the first example involved a classic and painful usecase (extracting 2 letters amid a string of numbers), which in Excel has to be written as: =LEFT(RIGHT(B18,LEN(B18)-FIND("-",B18)),FIND("-",RIGHT(B18,LEN(B18)-FIND("-",B18)))-1) But if only Excel would support regular expressions like Google Sheets [0], could be done as easily as: =REGEXEXTRACT(A2, "[A-Z]{2}") I'm sure adding regex isn't a trivial thing, but simple pattern extraction seems such an absolutely massive usecase for every everyday user that just I cannot fathom why Microsoft won't support regex. It would make Excel vastly more powerful for its purportedly non-coding users, especially since GSheets has had it for years now. Maybe someone on the product team believes regex feels too much like "code"? As a triple nested function involving subtr, strlen, and array indexing isn't? [0] https://support.google.com/docs/answer/3098244?hl=en https://support.google.com/docs/answer/3098244?hl=en
- deleted 6y ago
  [deleted]
- maxerickson 6y ago
  The regex is perhaps still clearer (and in general more powerful), but that Excel method is a catastrophe, not the way it has to be done. =MID(B1, FIND("-",B1)+1, 2)
- craftinator 6y ago
  > I'm sure adding regex isn't a trivial thing Actually, adding that into the list of Excel functions would be trivially easy. The hard part would be convincing all of the managers that it won't dramatically increase the amount of work they need to do in terms of tech support.
- darwingr 6y ago
  I found myself going back to this the other day. I learned these long left/right formulas before I learned to program. Rewriting them really made me miss regex.
CrazyCatDog 6y ago
Here’s hoping that the capability will be available for online excel (which otherwise pales in comparison to the pc client version)!
- MegaDeKay 6y ago
  It sounds like this is indeed the case. "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."
infogulch 6y ago
Wow this is cool! I felt that something like this could be transformative to excel for some time. 2014: https://news.ycombinator.com/item?id=8116224 https://news.ycombinator.com/item?id=8116224 > spreadsheets might be an interesting programming environment if you were restricted to the native functionality with a small addition. Namely, add a new value type: "anonymous function,"... 2019: https://news.ycombinator.com/item?id=21356824 https://news.ycombinator.com/item?id=21356824 > Excel needs exactly one thing to blow open the doors on productive programming: a new "function" data type. Since it's just a data type, you put it in a cell just like any other data type. Have some way to call it, like `A1(arg1, arg2)` or something. Now you can leverage the full capabilities of Excel to manage it, name it (named ranges), etc just like other data. ... From the same thread: > VBA is just an escape-hatch to a 'real' programming environment; my claim is that excel sheets & formulas alone could be a 'real' programming environment in its own right, no escape hatches necessary. I wonder if my comments inspired someone. :3
- Grustaf 6y ago
  Totally agree, we even applied to YC 5 years ago with an excel replacement specifically because of this limitation.
  - infogulch 6y ago
    The danger here is that MS can just observe your success and add the feature once you've proved it works, and your nice little blue gulf instantly turns red again. Though I might be interested in a redesign of the formula language; excel's is kinda crusty.
    - Grustaf 6y ago
      The idea had many downsides. But considering how little excel has changed in the 20 years I’ve used it, I wouldn’t be too worried about that particular issue!
      - infogulch 6y ago
oger 6y ago
While I welcome this development I must note from long-standing experience that using any type of more advanced functionality in Excel has always come back to bite me. The reason is exchange of workbooks with less skilled individuals. Or cross platform incompatibilities like URLENCODE not working on MacOS since many years now. And many use cases that once required Excel can now be solved by other tools with better documentation. The nature of Excel is in-auditability, intransparency and error proneness. Trust me - I‘ve been using it for too many years now.
ky3 6y ago
Apparently, the research literature calls this feature Sheet-Defined Functions (SDF). Doesn't it use this result from Microsoft Research UK that was just recently published in ICFP 2020: Elastic Sheet-Defined Functions: Generalising Spreadsheet Functions to Variable-Size Input Arrays https://icfp20.sigplan.org/details/icfp-2020-papers/46/Elastic-Sheet-Defined-Functions-Generalising-Spreadsheet-Functions-to-Variable-Size- https://icfp20.sigplan.org/details/icfp-2020-papers/46/Elast... Fastest beeline from research lab to the end-user I've ever seen.
ehejsbbejsk 6y ago
They need to support Python.
- mdm12 6y ago
  With Guido being a Microsoft employee now, that may just happen...
- askvictor 6y ago
  They (MS) have been talking about for a while: https://www.reddit.com/r/Python/comments/7jti46/ms_is_considering_official_python_integration/ https://www.reddit.com/r/Python/comments/7jti46/ms_is_consid... Yet to see anything tangible though.
orliesaurus 6y ago
Brings back all the Blockspring [0] vibes... You could create any cloud functions invoking external APIs and run them in Excel, so you could literally do anyhing.. My favorite use-case was pulling data from internal PRIVATE APIs to do statistical analysis...having up-to-date data every time you hit the refresh button was clutch! You could build dashboards inside excel with real-time data and save old data to do time-aware analysis of all kind stuff - literally speeding up so much time. Everyone knew how to use Excel, but very few people knew how to get API data into it by themselves. Anyone else was a huge fan? [0] http://blockspring.com/ http://blockspring.com/
- omneity 6y ago
  We’re bringing back some of that magic ... and more! https://monitoro.xyz https://monitoro.xyz (disclaimer, I’m the founder)
  - robertlagrant 6y ago
    That's cool. How does it work with website Ts & Cs, which generally don't like scraping?
  - orliesaurus 6y ago
    Cool, checking it out!
skrebbel 6y ago
This makes me unreasonably happy. Does anyone know how much time there usually is for Office stuff to go out of beta?
bencollier49 6y ago
A lot of this sort of functionality which is appearing at the moment from MS was built into a fantastic spreadsheet called ResolverOne which was released back in around 2008 by a company in the UK called Resolver Systems. It was based on IronPython and allowed an entire spreadsheet to be exported as a Python package. The company never seemed to gain traction, and unfortunately the open-source tool released which was based on ResolverOne had none of the power or elegance of the original. I'd be interested to know if MS had consulted Giles Thomas from R.S. prior to this - it's certainly giving me a bit of deja vu.
- bernardv 6y ago
  ResolverOne was an amazing product. It is too bad it never gained much support. Microsoft’s support of IronPython was half-hearted and it never realized its full potential. I don’t get excited for anything Microsoft does these days. MS simply caters to the lowest common denominator client and just doesn’t get its power users.
- layer8 6y ago
  IMO it's a pretty obvious feature -- functional abstraction. I was wondering for years why MS wouldn't add something like that. I guess they previously thought that VBA was enough.
daxfohl 6y ago
Lambda is kind of a weird name. Lambda functions are (traditionally) anonymous and close over local variables. These are just UDFs in Excel syntax, which seems like nothing super exciting (surprised it didn't exist already). I'd be curious what an actual lambda thing would look like in Excel.
- ripley12 6y ago
  =LAMBDA(...) does return an anonymous function - and then you use the Names Manager to name it. It is a bit weird and confusing because you currently can’t really use the anonymous functions without naming them, but maybe they’re going to relax that restriction eventually?
  - MegaDeKay 6y ago
    The article also says this is valid within the grid: 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) Edit: typo
    - ripley12 6y ago
      Yeah, that’s why I added a qualifier. I can’t see that being much use outside of testing/debugging functions, since it would usually be simpler to just write that without the LAMBDA call.
      - int_19h 6y ago
        They can also be referenced via the cells. You could argue that cells are themselves named, but those names are really indices into a 2D array, you can have relative references or do offset-based math if needed, and there's indirection (i.e. you can use an arbitrary value as an index to read/write another value). Given all this, an index of a cell containing a lambda is really a lot like a function reference, and can be used in much the same way - passed around etc. So, these are anonymous functions. The more interesting question is whether they're closures - that is, whether a LAMBDA nested in another LAMBDA can reference the latter's parameters, and how it interacts with LET (https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...).
jtsuken 6y ago
Microsoft: Hey, we have this new feature. It's called Macros. You can execute any code you like and use it as functions in your spreadsheets. Users: Great! Let's start using it everywhere! Users: Hey! Our spreadsheets have become very slow and hackers break into our systems by executing arbitrary code in our spreadsheets Microsoft: OK! From now on you will have to save workbooks that can execute arbitrary code in a dedicated file format, which will only open after showing 15 warning messages. .... Microsoft: _Hey, we have this new feature. It's called Lambda. You can execute any code you like and use it as functions in your spreadsheets._
- layer8 6y ago
  The problem with “macros” is that they can be arbitrary VBA code that can invoke OS functions and foreign applications. Lambdas can only invoke Excel functions that you can invoke anyway from any Excel cell. Lambdas merely add an abstraction mechanism, they otherwise don’t provide access to new functionality.
  - samfisher83 6y ago
    This is what they said: new capability that will revolutionize how you build formulas in Excel Which isn't really true. I can call macros using the =function(x) capability like forever.
    - layer8 6y ago
      Macros are not “formulas in Excel”, lambdas are.
    - Closi 6y ago
      The differences seem to be: * You can write it in one language (excel formula language) * The language is simpler and known by almost all users, while Javascript and VBA are only used by a tiny proportion of users. * The language is more secure (i.e. you can't execute arbitrary code, access files, call DLLs etc) * Because of the above, users don't need any security permissions / get warnings when running it. * Because they are standard excel formulas, you get OOTB support for other excel features such as dynamic array formulas and access to the full catalogue of worksheet functions (even in VBA, Application.Worksheet only had access to a few basic excel workbook functions, so if you wanted to do a Xlookup for example you are implementing it yourself with arrays and loops)
askvictor 6y ago
I can see this being a good thing; but would really like to see both debugging and inbuilt testing (could be done right in the name manager - a box for inputs and expected output)
- MegaDeKay 6y ago
  They hint at something like this coming in the comments. Do you have plans in foreseeable future to bring those features? Formula formatting, debugging (at least with F9), code navigation (jump to function definition, etc) and so on. I completely hear you on this one! I can't share more about what we are doing in the future but I will say that I definitely share your sentiment. I would love to see us add much needed tools for debugging and authoring formulas. Akin to what you get with great IDEs.
rrjjww 6y ago
As someone who sends .xlsx files back and forth with clients often, I’m most concerned about compatibility if I start integrating this into my worksheets. It sounds like those clients that haven’t updated to the latest Excel will receive a mess of #CALC errors. Otherwise an exciting development.
MauranKilom 6y ago
So, who'll be the first to build a Y-combinator in Excel?
kqvamxurcagg 6y ago
I use excel a lot and I think I'll struggle to find a use case for this. As others have mentioned, this is essentially a user-defined function. I generally shy away from these as it will make it difficult to share spreadsheets with others as they may not even know what lambdas are. Auditing lambdas will be a nightmare, as will tracing dependencies.
zupa-hu 6y ago
This is a game-changer level feature. I have been dreaming of this and more other power features for a long while. Well, except for the Name Manager part in their implementation, which seems to be a total disaster. I really hope this is a first version and they are going to keep improving it as they say. One should really define the functions in the cells and be able to reference them like =A1(2). The last thing a beautiful functional environment needs is globals. Super curious what the future of Excel holds.
- interblag 6y ago
  Totally agree - this could actually be a really natural bridge into programming for a lot of people whose advanced Excel skills already have them on the cusp. Name Manager though... Would be really nice if they could come up with some idioms for writing these functions in a multi-line format, with indentation, and give a slightly nicer editor. I realize that might be a bit tricky without changing the language syntax, but after the 2nd nested if-statement I find that I really struggle to follow someone's single-line Excel logic...
  - zupa-hu 6y ago
    No need to change the syntax! I’ve worked with a financialy company that had really complex logic in there. Every time I had to touch one, I copied it into a code editor and gave it proper indentation, pretty much one variable at a line. Such clarity. I believe one could just paste it back and it worked - though I can’t recall for sure.
  - pjgalbraith 6y ago
    They also announced the LET expression which helps a bit with the formatting problem - https://techcommunity.microsoft.com/t5/excel-blog/let-names-in-formulas-generally-available/ba-p/1878903 https://techcommunity.microsoft.com/t5/excel-blog/let-names-...
- daxfohl 6y ago
  Frankly Name Manager could also be way useful if they just generified it. Make it so that you can name a cell. Then,l you get named "variables" and, with your proposed lambda implementation, you get both named and anonymous functions in one. They are orthogonal features. As is, yes, Name Manager seems very ugly.
shostack 6y ago
I always felt VB code and macros were buried away somewhere that made it hard for less technical people to use. This seems like it lowers the learning curve for, at the very least, adding DRY principals to more every day use cases. I consider myself fairly comfortable in Excel. The number of times I've been burned in my own (or more likely shared) spreadsheet by things like copying down a formula that got modified in one instance but not all and related issues is staggering. Being able to have some cells where core logic lives makes it easier for less technical people to understand what's going on, and makes formulae a lot more reusable.
layer8 6y ago
The following bit from the documentation [0] ("step 2") is a bit strange: > A good practice is to create and test your LAMBDA function in a cell to make sure it works correctly, including the definition and the passing of parameters. To avoid the #CALC! error, add a call to the LAMBDA function to immediately return the result: > =LAMBDA function ([parameter1, parameter2, ...],calculation) (function call) > The following example returns a value of 2. > =LAMBDA(number, number + 1)(1) > Assuming the LAMBDA function is in cell A1, you can reference the cell that contains the LAMBDA function in the following way: > =A1(1) What this seems to say is that (1) you get a #CALC! error when a cell contains a bare =LAMBDA(...) expression, and (2) if the lambda calls itself (doesn't produce a #CALC! error any more), then you can call it with a different argument by referencing the cell containing the self-invocation (the "=A1(1)" example above). This seems like a weird model, because just "=A1" would give you the result of the self-invocation. Maybe the documentation intends to say that you can do the "=A1(1)" call iff the cell containing the lambda is not a self-invocation (but then shows the #CALC! error)? [0] https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67 https://support.microsoft.com/en-us/office/lambda-function-b...
- int_19h 6y ago
  (1) is explicitly spelled out: > If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error. For (2), I think what they're saying is that if you need to test the function with different arguments, for example, it may be more convenient to reference the cell with the lambda.
tzesti 6y ago
I wish Excel had a more strict typing. Side-effect free functional language (maybe F#?) as the formula syntax would also be very nice. Higher order functions... Maybe I am starting to ask too much.
lhoff 6y ago
When will the WASM runtime will be available.
- shp0ngle 6y ago
  Excel query language is now turing complete, so I think WASM runtime can now be emulated in Excel. I think Excel can now run QEMU, come to think of it. (without macros. With macros, Excel can run anything.)
Craighead 6y ago
Nice! this is a great tool for the folks that need more power tools in their daily app usage environment
MarcScott 6y ago
I remember talking to Simon Peyton Jones a few years ago, and he mentioned that he'd like Excel to be the first functional language that kids get to use. Like a gateway drug to Haskell. Looks like they're getting there.
darwingr 6y ago
Finally! This is something I've been wanting for years. There's always been this midpoint of complexity when making spreadsheets that coworkers will use. Including macros scares them away but using long, un-named formulas does not even though it is much less clear what it's doing. The full power of a macro was not needed, only a label and some arguments to lower the cognitive load.
- darwingr 6y ago
  My prediction of the next most obvious built-in function after lambda and let: `=register(name, reference_or_formula, [scope])` that registers a defined name for the spreadsheet with code rather than the "Define Name" box.
  - oezi 6y ago
    Absolutely right, the use of the name manager makes this much less usable. Instead of being able to copy lambdas from cells, you now also have to set their name manually.

223 points by lelf 6y ago

rajandatta 6y ago

This is extremely significant to simplifying spreadsheets.

tgv 6y ago
Yes, but there is no mention of debugging. In the hands of Excel cowboys, this can become another foot gun.
- bombcar 6y ago
  A number of bugs will be solved by moving to it (a copied function is typo’d in one cell, etc) and you can test it pretty easily in a cell. Excel is basically a REPL with cells.
  - robertlagrant 6y ago
    A REPL treated as a software system.
- int_19h 6y ago
  Between this and LET (https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...), Excel seems to be a full-fledged pure functional language now, with lexical scoping, first-class and high-order functions etc.
bombcar 6y ago
I see it making spreadsheets easier to convert into code, too. Many “business programs” begin as a spreadsheet.
- lambda_obrien 6y ago
  We have services which run a "notebook as a service" so it's not too far fetched to think of Microsoft doing this with Excel spreadsheets. I bet it would take the business world by storm and would be a good move.
- robertlagrant 6y ago
  Next up: Excel functions running as Azure Functions in the cloud.
  - avandvrnot 6y ago
    I assumed that was what this is!

dsalzman 6y ago

I hope they add an import and export function to this. Ideally you could sync these functions at an org level via O365.

ryanmarsh 6y ago
Then you need version control. Better to let the document be the boundary of consistency.
- toomuchtodo 6y ago
  Or support versioning using GitHub! Excel has always been a powerful repl, these extensions are leveling it up.
  - geogra4 6y ago
    This would make Excel 100x more maintainable. I would think a nice Github integration would turn excel closer to a maintainable programming language.
    - deleted 6y ago
      [deleted]

ryanmarsh 6y ago

Does Google sheets let you do anything like this?

cdolan 6y ago
Yes it supports JavaScript functions that can be called in the formula bar. However Google sheets is nowhere the install base of excel, so this is a really big deal
- Closi 6y ago
  Excel already has the ability to write custom functions in Javascript (and VBA), this is the ability to write them in the formula language rather than JavaScript. (So no, Google Sheets does not support this) The latest improvements in Excel really do seem to be re-widening the gap between Google Sheets and Excel (Dynamic array formulas, Let, Custom data types, powerquery improvements...)
- ttul 6y ago
  Is it not, though? Google Sheets is available to all GSuite customers. GSuite is the most popular email hosting platform in the world, with roughly 18% market share (Microsoft 365 trails not significantly far behind). I think Google Sheets has a pretty solid user base.
  - cdolan 6y ago
    GSuite and Office 365 subscription base, while somewhat correlated to, is not equal to the usage of Excel vs Google Sheets. Highly anecdotal, but there are far more complex business processes still running in Excel that are not going to be translated over to Google sheets, and they are all offline behind a network firewall. Those types of sheets will really benefit from this improvement. Now if they just supported python instead of VBA for scripting!
- cpitman 6y ago
  As already pointed out, these are not the same. Javascript based custom functions in Google Sheets are actually really error prone, meaning that they will just _randomly fail to execute_. Cells will be stuck with a "loading..." message until you trick Sheets into recalculating the value. In the next couple months, I'll likely be ripping out as many custom functions as I can. And these are not for particularly complicated functions. I'd really love to have this feature in Sheets. It would simplify a lot of what my sheets do, and also make it more accessible to a Sheets power user that gets scared off from code.

geocar 6y ago

I love the branding "custom functions without code" right before a bunch of code. Reminds me how early word processors (the person, not the software) were convinced to program word processors (the software this time) just by calling the programs "macros". I'm not joining Microsoft's beta program right now, but I'm curious if anyone knows the data type of a =LAMBDA?

bitwize 6y ago
In the 1970s, secretaries wrote their own extensions to Emacs in Lisp. They were only ever told they were "customizing" the editor, not programming it! (This was Multics Emacs, a predecessor to GNU Emacs.)
- protomyth 6y ago
  "Customizing" is something everyone loves to do in a lot of aspects of our lives. Programming is for those weirdos in IT. Building stacks in HyperCard wasn't programming either it was just writing interactive presentations.
indymike 6y ago
Hmm. A lambda platform that just runs spreadsheets. This is a killer idea.
- fiddlerwoaroof 6y ago
  This is what I thought it was: upload spreadsheet, get API

kevin_thibedeau 6y ago

They sort of already had this with the old style pre-VBA Excel macros.

JoBrad 6y ago
This might be better, though. Because it lets you use the same spreadsheet functions, in the spreadsheet. It would be trivial to make a lamdha function that encapsulates several spreadsheet functions into one.

croes 6y ago

Yeah, let's make the moloch even more unmaintainable. Does still have the leap year error to be compatible with Lotus?

djeiasbsbo 6y ago
What bothers me greatly about Excel is something that native English speakers perhaps have never had to deal with. The "keywords" are language specific/dependent. Everytime I have to google how to do a specific thing in Excel I then have to spend 5 times longer to translate the instructions into my language. It is a problem because in a locked down corporate environment, a worker/user cannot easily change the language.
- vic20forever 6y ago
  I think the Functions Translator[1][2] add-in might be worth a look to you. Note: I haven't used it myself, and it's a Microsoft Garage project, so there's no guarantee of support or maintenance. From the description: Functions Translator helps people use a localized version of Excel by helping translate from the US Excel function names, or research how to create a solution on the web with predominately English content. Easily find the equivalent localized functions and formulas in any of the supported 15 languages. Functions Translator will automatically configure the language settings to US and the Localized version, and people can provide feedback on the translation of functions if it is not what they expected. [1] https://www.microsoft.com/en-us/garage/blog/2018/03/new-garage-project-enables-excel-users-to-work-seamlessly-with-functions-and-formulas-across-all-translated-versions-of-excel/ https://www.microsoft.com/en-us/garage/blog/2018/03/new-gara... [2] https://www.microsoft.com/en-us/garage/profiles/functions-translator/ https://www.microsoft.com/en-us/garage/profiles/functions-tr...
MegaDeKay 6y ago
This lets you do things like localize a complex formula to one spot that might otherwise be used many times within a workbook. How does that make things more unmaintainable?

DavidPeiffer 6y ago

As a frequent user of Excel, I very much welcome this. Being able to build a function library beyond User Defined (UDF, VBA functions that can be called from a formula) and a Personal.xlsb file (VBA that's available in any open Excel file) One common use case this helps takes the general form "If A2+B2 > 10,then A2+B2, else 10". "A2+B2" needs to be updated, it has to be updated twice. Alternately, you could have a "helper column" C2=A2+B2, but this adds clutter to the whole spreadsheet. I have a number of UDF's which lend themselves well to this, such as a triangular distribution calculator. Execution through Lambda should allow the undo stack to continue working (normally ditched by executing VBA) and hopefully give a performance boost.

sheetjs 6y ago
For that type of pattern, you can also use the new LET function: LET(myval, A2+B2, IF(myval>10, myval, 10)) https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...
- DavidPeiffer 6y ago
  Thanks! Haven't sunk my teeth into the new round of features yet, but have found great utility from the last batch (unique, filter, sort) and the prior batch.
an_opabinia 6y ago
Isn’t that just =MAX(A2+B2,10)? Anyway I know what you mean. It seems fine to make a sheet that behaves as variables. Ie a sheet that is two columns, the comment for an intermediate and its values. Reference it elsewhere. Re-engineering the whole Excel as a website, with its attendant sandboxing, seems to be the future.
- rahimnathwani 6y ago
  This reminds me of the IFERROR(). Without IFERROR, a common pattern would be IF(ISERROR(A2+B2),0,A2+B2). With IFERROR, you can just do IFERROR(A2+B2,0)

ttul 6y ago

The number one reason that I use Google Sheets instead of Excel is the availability of the REGEXMATCH and REGEXEXTRACT functions. No human should forced to use a ridiculous combination of LEFT, RIGHT, and MID to extract things from a string. I just can’t fathom why Excel hasn’t yet introduced regular expressions.

cdolan 6y ago
Agreed. I would love to leverage REGEX! Excel formulas have long been too hard to comprehend if you were not the original author... and even if you did author it, 4 weeks later you won’t remember how it worked without a half hour of review!
chaz6 6y ago
Whilst not native, you can emulate these with a user defined function (UDF).
cm2187 6y ago
Given the number of developers who spit on the floor as soon as they hear “regex” (including me), I am not sure regex will bring much to regular users. If it is hard to developers, it is impossible to regular users. (And if you really want regex, there are hundreds of results on google on how to build your own VBA UDF to get it).
- aidos 6y ago
  There’s nothing wrong with a small extract or substitute regex, in my opinion.
- craftinator 6y ago
  > Given the number of developers who spit on the floor as soon as they hear “regex” > If it is hard to developers, it is impossible to regular users. What are you talking about? Regular expressions are used everywhere. On the backend it's text parsing, on the front end it's input validation. I have never written a complete application without using it. You're also the first person I've heard grumble about them. I get that if regex is used in an overly convoluted or messy fashion, they become unreadable and unreliable. Just like assignment operators, nested division, or any other basic programming construct. But they are also remarkably powerful at solving simple pattern matching in a robust way. I recommend you go learn them instead of making baseless claims about "the number of developers who spit on the floor" when talking about them or whatever.

danso 6y ago

This is completely orthogonal to the full scope of LAMBDA, But it did make me laugh to see that the first example involved a classic and painful usecase (extracting 2 letters amid a string of numbers), which in Excel has to be written as: =LEFT(RIGHT(B18,LEN(B18)-FIND("-",B18)),FIND("-",RIGHT(B18,LEN(B18)-FIND("-",B18)))-1) But if only Excel would support regular expressions like Google Sheets [0], could be done as easily as: =REGEXEXTRACT(A2, "[A-Z]{2}") I'm sure adding regex isn't a trivial thing, but simple pattern extraction seems such an absolutely massive usecase for every everyday user that just I cannot fathom why Microsoft won't support regex. It would make Excel vastly more powerful for its purportedly non-coding users, especially since GSheets has had it for years now. Maybe someone on the product team believes regex feels too much like "code"? As a triple nested function involving subtr, strlen, and array indexing isn't? [0] https://support.google.com/docs/answer/3098244?hl=en https://support.google.com/docs/answer/3098244?hl=en

deleted 6y ago
[deleted]
maxerickson 6y ago
The regex is perhaps still clearer (and in general more powerful), but that Excel method is a catastrophe, not the way it has to be done. =MID(B1, FIND("-",B1)+1, 2)
craftinator 6y ago
> I'm sure adding regex isn't a trivial thing Actually, adding that into the list of Excel functions would be trivially easy. The hard part would be convincing all of the managers that it won't dramatically increase the amount of work they need to do in terms of tech support.
darwingr 6y ago
I found myself going back to this the other day. I learned these long left/right formulas before I learned to program. Rewriting them really made me miss regex.

CrazyCatDog 6y ago

Here’s hoping that the capability will be available for online excel (which otherwise pales in comparison to the pc client version)!

MegaDeKay 6y ago
It sounds like this is indeed the case. "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."

infogulch 6y ago

Wow this is cool! I felt that something like this could be transformative to excel for some time. 2014: https://news.ycombinator.com/item?id=8116224 https://news.ycombinator.com/item?id=8116224 > spreadsheets might be an interesting programming environment if you were restricted to the native functionality with a small addition. Namely, add a new value type: "anonymous function,"... 2019: https://news.ycombinator.com/item?id=21356824 https://news.ycombinator.com/item?id=21356824 > Excel needs exactly one thing to blow open the doors on productive programming: a new "function" data type. Since it's just a data type, you put it in a cell just like any other data type. Have some way to call it, like `A1(arg1, arg2)` or something. Now you can leverage the full capabilities of Excel to manage it, name it (named ranges), etc just like other data. ... From the same thread: > VBA is just an escape-hatch to a 'real' programming environment; my claim is that excel sheets & formulas alone could be a 'real' programming environment in its own right, no escape hatches necessary. I wonder if my comments inspired someone. :3

Grustaf 6y ago
Totally agree, we even applied to YC 5 years ago with an excel replacement specifically because of this limitation.
- infogulch 6y ago
  The danger here is that MS can just observe your success and add the feature once you've proved it works, and your nice little blue gulf instantly turns red again. Though I might be interested in a redesign of the formula language; excel's is kinda crusty.
  - Grustaf 6y ago
    The idea had many downsides. But considering how little excel has changed in the 20 years I’ve used it, I wouldn’t be too worried about that particular issue!
    - infogulch 6y ago

oger 6y ago

While I welcome this development I must note from long-standing experience that using any type of more advanced functionality in Excel has always come back to bite me. The reason is exchange of workbooks with less skilled individuals. Or cross platform incompatibilities like URLENCODE not working on MacOS since many years now. And many use cases that once required Excel can now be solved by other tools with better documentation. The nature of Excel is in-auditability, intransparency and error proneness. Trust me - I‘ve been using it for too many years now.

ky3 6y ago

Apparently, the research literature calls this feature Sheet-Defined Functions (SDF). Doesn't it use this result from Microsoft Research UK that was just recently published in ICFP 2020: Elastic Sheet-Defined Functions: Generalising Spreadsheet Functions to Variable-Size Input Arrays https://icfp20.sigplan.org/details/icfp-2020-papers/46/Elastic-Sheet-Defined-Functions-Generalising-Spreadsheet-Functions-to-Variable-Size- https://icfp20.sigplan.org/details/icfp-2020-papers/46/Elast... Fastest beeline from research lab to the end-user I've ever seen.

ehejsbbejsk 6y ago

They need to support Python.

mdm12 6y ago
With Guido being a Microsoft employee now, that may just happen...
askvictor 6y ago
They (MS) have been talking about for a while: https://www.reddit.com/r/Python/comments/7jti46/ms_is_considering_official_python_integration/ https://www.reddit.com/r/Python/comments/7jti46/ms_is_consid... Yet to see anything tangible though.

orliesaurus 6y ago

Brings back all the Blockspring [0] vibes... You could create any cloud functions invoking external APIs and run them in Excel, so you could literally do anyhing.. My favorite use-case was pulling data from internal PRIVATE APIs to do statistical analysis...having up-to-date data every time you hit the refresh button was clutch! You could build dashboards inside excel with real-time data and save old data to do time-aware analysis of all kind stuff - literally speeding up so much time. Everyone knew how to use Excel, but very few people knew how to get API data into it by themselves. Anyone else was a huge fan? [0] http://blockspring.com/ http://blockspring.com/

omneity 6y ago
We’re bringing back some of that magic ... and more! https://monitoro.xyz https://monitoro.xyz (disclaimer, I’m the founder)
- robertlagrant 6y ago
  That's cool. How does it work with website Ts & Cs, which generally don't like scraping?
- orliesaurus 6y ago
  Cool, checking it out!

skrebbel 6y ago

This makes me unreasonably happy. Does anyone know how much time there usually is for Office stuff to go out of beta?

bencollier49 6y ago

A lot of this sort of functionality which is appearing at the moment from MS was built into a fantastic spreadsheet called ResolverOne which was released back in around 2008 by a company in the UK called Resolver Systems. It was based on IronPython and allowed an entire spreadsheet to be exported as a Python package. The company never seemed to gain traction, and unfortunately the open-source tool released which was based on ResolverOne had none of the power or elegance of the original. I'd be interested to know if MS had consulted Giles Thomas from R.S. prior to this - it's certainly giving me a bit of deja vu.

bernardv 6y ago
ResolverOne was an amazing product. It is too bad it never gained much support. Microsoft’s support of IronPython was half-hearted and it never realized its full potential. I don’t get excited for anything Microsoft does these days. MS simply caters to the lowest common denominator client and just doesn’t get its power users.
layer8 6y ago
IMO it's a pretty obvious feature -- functional abstraction. I was wondering for years why MS wouldn't add something like that. I guess they previously thought that VBA was enough.

daxfohl 6y ago

Lambda is kind of a weird name. Lambda functions are (traditionally) anonymous and close over local variables. These are just UDFs in Excel syntax, which seems like nothing super exciting (surprised it didn't exist already). I'd be curious what an actual lambda thing would look like in Excel.

ripley12 6y ago
=LAMBDA(...) does return an anonymous function - and then you use the Names Manager to name it. It is a bit weird and confusing because you currently can’t really use the anonymous functions without naming them, but maybe they’re going to relax that restriction eventually?
- MegaDeKay 6y ago
  The article also says this is valid within the grid: 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) Edit: typo
  - ripley12 6y ago
    Yeah, that’s why I added a qualifier. I can’t see that being much use outside of testing/debugging functions, since it would usually be simpler to just write that without the LAMBDA call.
    - int_19h 6y ago
      They can also be referenced via the cells. You could argue that cells are themselves named, but those names are really indices into a 2D array, you can have relative references or do offset-based math if needed, and there's indirection (i.e. you can use an arbitrary value as an index to read/write another value). Given all this, an index of a cell containing a lambda is really a lot like a function reference, and can be used in much the same way - passed around etc. So, these are anonymous functions. The more interesting question is whether they're closures - that is, whether a LAMBDA nested in another LAMBDA can reference the latter's parameters, and how it interacts with LET (https://support.microsoft.com/en-us/office/let-function-34842dd8-b92b-4d3f-b325-b8b8f9908999 https://support.microsoft.com/en-us/office/let-function-3484...).

jtsuken 6y ago

Microsoft: Hey, we have this new feature. It's called Macros. You can execute any code you like and use it as functions in your spreadsheets. Users: Great! Let's start using it everywhere! Users: Hey! Our spreadsheets have become very slow and hackers break into our systems by executing arbitrary code in our spreadsheets Microsoft: OK! From now on you will have to save workbooks that can execute arbitrary code in a dedicated file format, which will only open after showing 15 warning messages. .... Microsoft: _Hey, we have this new feature. It's called Lambda. You can execute any code you like and use it as functions in your spreadsheets._

layer8 6y ago
The problem with “macros” is that they can be arbitrary VBA code that can invoke OS functions and foreign applications. Lambdas can only invoke Excel functions that you can invoke anyway from any Excel cell. Lambdas merely add an abstraction mechanism, they otherwise don’t provide access to new functionality.
- samfisher83 6y ago
  This is what they said: new capability that will revolutionize how you build formulas in Excel Which isn't really true. I can call macros using the =function(x) capability like forever.
  - layer8 6y ago
    Macros are not “formulas in Excel”, lambdas are.
  - Closi 6y ago
    The differences seem to be: * You can write it in one language (excel formula language) * The language is simpler and known by almost all users, while Javascript and VBA are only used by a tiny proportion of users. * The language is more secure (i.e. you can't execute arbitrary code, access files, call DLLs etc) * Because of the above, users don't need any security permissions / get warnings when running it. * Because they are standard excel formulas, you get OOTB support for other excel features such as dynamic array formulas and access to the full catalogue of worksheet functions (even in VBA, Application.Worksheet only had access to a few basic excel workbook functions, so if you wanted to do a Xlookup for example you are implementing it yourself with arrays and loops)

askvictor 6y ago

I can see this being a good thing; but would really like to see both debugging and inbuilt testing (could be done right in the name manager - a box for inputs and expected output)

MegaDeKay 6y ago
They hint at something like this coming in the comments. Do you have plans in foreseeable future to bring those features? Formula formatting, debugging (at least with F9), code navigation (jump to function definition, etc) and so on. I completely hear you on this one! I can't share more about what we are doing in the future but I will say that I definitely share your sentiment. I would love to see us add much needed tools for debugging and authoring formulas. Akin to what you get with great IDEs.

rrjjww 6y ago

As someone who sends .xlsx files back and forth with clients often, I’m most concerned about compatibility if I start integrating this into my worksheets. It sounds like those clients that haven’t updated to the latest Excel will receive a mess of #CALC errors. Otherwise an exciting development.

MauranKilom 6y ago

So, who'll be the first to build a Y-combinator in Excel?

kqvamxurcagg 6y ago

I use excel a lot and I think I'll struggle to find a use case for this. As others have mentioned, this is essentially a user-defined function. I generally shy away from these as it will make it difficult to share spreadsheets with others as they may not even know what lambdas are. Auditing lambdas will be a nightmare, as will tracing dependencies.

zupa-hu 6y ago

This is a game-changer level feature. I have been dreaming of this and more other power features for a long while. Well, except for the Name Manager part in their implementation, which seems to be a total disaster. I really hope this is a first version and they are going to keep improving it as they say. One should really define the functions in the cells and be able to reference them like =A1(2). The last thing a beautiful functional environment needs is globals. Super curious what the future of Excel holds.

interblag 6y ago
Totally agree - this could actually be a really natural bridge into programming for a lot of people whose advanced Excel skills already have them on the cusp. Name Manager though... Would be really nice if they could come up with some idioms for writing these functions in a multi-line format, with indentation, and give a slightly nicer editor. I realize that might be a bit tricky without changing the language syntax, but after the 2nd nested if-statement I find that I really struggle to follow someone's single-line Excel logic...
- zupa-hu 6y ago
  No need to change the syntax! I’ve worked with a financialy company that had really complex logic in there. Every time I had to touch one, I copied it into a code editor and gave it proper indentation, pretty much one variable at a line. Such clarity. I believe one could just paste it back and it worked - though I can’t recall for sure.
- pjgalbraith 6y ago
  They also announced the LET expression which helps a bit with the formatting problem - https://techcommunity.microsoft.com/t5/excel-blog/let-names-in-formulas-generally-available/ba-p/1878903 https://techcommunity.microsoft.com/t5/excel-blog/let-names-...
daxfohl 6y ago
Frankly Name Manager could also be way useful if they just generified it. Make it so that you can name a cell. Then,l you get named "variables" and, with your proposed lambda implementation, you get both named and anonymous functions in one. They are orthogonal features. As is, yes, Name Manager seems very ugly.

shostack 6y ago

I always felt VB code and macros were buried away somewhere that made it hard for less technical people to use. This seems like it lowers the learning curve for, at the very least, adding DRY principals to more every day use cases. I consider myself fairly comfortable in Excel. The number of times I've been burned in my own (or more likely shared) spreadsheet by things like copying down a formula that got modified in one instance but not all and related issues is staggering. Being able to have some cells where core logic lives makes it easier for less technical people to understand what's going on, and makes formulae a lot more reusable.

layer8 6y ago

The following bit from the documentation [0] ("step 2") is a bit strange: > A good practice is to create and test your LAMBDA function in a cell to make sure it works correctly, including the definition and the passing of parameters. To avoid the #CALC! error, add a call to the LAMBDA function to immediately return the result: > =LAMBDA function ([parameter1, parameter2, ...],calculation) (function call) > The following example returns a value of 2. > =LAMBDA(number, number + 1)(1) > Assuming the LAMBDA function is in cell A1, you can reference the cell that contains the LAMBDA function in the following way: > =A1(1) What this seems to say is that (1) you get a #CALC! error when a cell contains a bare =LAMBDA(...) expression, and (2) if the lambda calls itself (doesn't produce a #CALC! error any more), then you can call it with a different argument by referencing the cell containing the self-invocation (the "=A1(1)" example above). This seems like a weird model, because just "=A1" would give you the result of the self-invocation. Maybe the documentation intends to say that you can do the "=A1(1)" call iff the cell containing the lambda is not a self-invocation (but then shows the #CALC! error)? [0] https://support.microsoft.com/en-us/office/lambda-function-bd212d27-1cd1-4321-a34a-ccbf254b8b67 https://support.microsoft.com/en-us/office/lambda-function-b...

int_19h 6y ago
(1) is explicitly spelled out: > If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error. For (2), I think what they're saying is that if you need to test the function with different arguments, for example, it may be more convenient to reference the cell with the lambda.

tzesti 6y ago

I wish Excel had a more strict typing. Side-effect free functional language (maybe F#?) as the formula syntax would also be very nice. Higher order functions... Maybe I am starting to ask too much.

lhoff 6y ago

When will the WASM runtime will be available.

shp0ngle 6y ago
Excel query language is now turing complete, so I think WASM runtime can now be emulated in Excel. I think Excel can now run QEMU, come to think of it. (without macros. With macros, Excel can run anything.)

Craighead 6y ago

Nice! this is a great tool for the folks that need more power tools in their daily app usage environment

MarcScott 6y ago

I remember talking to Simon Peyton Jones a few years ago, and he mentioned that he'd like Excel to be the first functional language that kids get to use. Like a gateway drug to Haskell. Looks like they're getting there.

darwingr 6y ago

Finally! This is something I've been wanting for years. There's always been this midpoint of complexity when making spreadsheets that coworkers will use. Including macros scares them away but using long, un-named formulas does not even though it is much less clear what it's doing. The full power of a macro was not needed, only a label and some arguments to lower the cognitive load.

darwingr 6y ago
My prediction of the next most obvious built-in function after lambda and let: `=register(name, reference_or_formula, [scope])` that registers a defined name for the spreadsheet with code rather than the "Define Name" box.
- oezi 6y ago
  Absolutely right, the use of the name manager makes this much less usable. Instead of being able to copy lambdas from cells, you now also have to set their name manually.