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._
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.
* 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)
I don’t think it’s misleading, I mean everyone’s definition of “revolutionary” is different, but let’s put it into perspective: this is adding functions and recursion to one of the worlds most popular data analysis languages.
I think most people on this site know how powerful the concepts of functions and recursion can be!
Adding to what others have replied, the difference is simply put: If you can write your custom function as a lambda instead of a VBA function you will not have to save your Workbook as XLSM (M for Macro). That means no warnings when the user opens it and much better security because the lambdas can only call built-in functions and other lambdas.
To be honest, I think this is awesome and has been sorely missed.
It’s absolutely true. Most spreadsheets in BigCo are viewed by at least 3 or 4 people. No one likes it when they open a spreadsheet from someone else with a macro because it dramatically increases the learning curve.
If you just had custom functions, you can trace them back pretty quickly and end up with an understanding. Teams will also probably create ‘known’ custom functions for their use case, like converting account Chart of Account codes to finance COA codes etc.
It seems obvious to me why "VBA in a cell" is a nonstarter and why making this a formula-level feature is necessary. I'm glad macros have worked for whatever use case in the past, but it can never be a capital-f Feature of excel because it's a security nightmare and microsoft and everyone that uses macros knows this (or should).
If you want a platform to succeed, make it capable of satisfying most users needs within its sandbox; using macros is just giving up and working around it.
Allow me to reminisce.. in 2012 I created a JavaScript VM extension that executes custom functions inside native Excel. The extension also had a corresponding web-based front-end for creating apps that read/write directly to Excel. This was my baby, but investors (ie, YC) didn't think it was disruptive enough. Now I see these apps (ie, AirTable) pop up every month. Should I open source it?
I don't want to be cynical, but they could have done this with reason. They could have also called it functions or methods. But chose to disrupt search results for a competitor.
Your anti-M$FT bias is showing. AMZN struck first... Microsoft was using the term Lambda for a prominent feature in its flagship language (C#) before AWS ever existed.
> 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,"...
> 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.
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.
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!
Actually what might be better is "sheet functions". So like pages where you can define input and output cells. Then you top level sheet can invoke the "function" as like "=myfunctionsheet{A1=5, A2=9}.N8". That way you get local scope, etc, and you can do more complex stuff because it's very visual instead of being a mathematical function.
Obviously this will be best when used with Name Manager (especially if names can be scoped to the sheet) to name the input and output cells, rather than indexing off the cell ids directly.
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.
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.
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.
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?
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.)
"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.
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
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.
=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?
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:
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.
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-3484...).
Huh, are you sure? I don’t think you can (currently) define a lambda in a cell without also calling it in that cell (which means the cell now evaluates to the function’s return value, not a function). The LAMBDA docs:
“If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.”
It will show #CALC! for that cell because, well, it has to show something for the result of the formula - but the formula didn't evaluate to a "displayable" value.
Yeah, I was expecting you to be able to define a lambda in a cell, so like `B1` could be `=LAMBDA(x => $A1 + x + 1)`. Then you could assign another cell say `D1` to like `$B1($C1)`. Drag-filling B1 down would make say `B2` equal to `=LAMBDA(x => $A2 + x + 2)` or something, so that it would work like people would think Excel Lambdas should.
That would be more along the lines of lambdas in the traditional sense of anonymous functions, closure captures, higher order functions(?), etc. Could be cool, but IDK if it would be useful? Can't think of any specific use cases right now, but perhaps once people get used to it, there would be tons of them.
You can, but you can't (currently?) do =A1(x) where A1 is a LAMBDA function. I think LAMBDA(...)(x) is only useful for testing/development, but maybe I'm missing some good use cases.
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.
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...
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.
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.
Actually what might be better is sheet functions. So like pages where you can define input and output cells. Then you top level sheet can invoke the "function" as like "=mypage(A1, A2).N8". That way you get local scope, etc, and even excel noobs could figure it out because it's very visual instead of being a mathematical function.
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)?
> 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.
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:
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?
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
"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."
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.
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.
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.
And IFERROR() wasn't added until Excel 2007. The other way to make that operation less unwieldy if the expression was complicated, to avoid repeating it I'd put that inside another cell, and that way it would be =IF(ISERROR(C2),0,C2), but IFERROR is a much better solution.
Excel is such a good tool in many ways, and such a bad tool in many ways. Really experienced power users can follow Excel formulas much easier than blocks of imperative code. But the more complicated it gets, the harder it can be to follow it all. But that also goes the same for software, too. I do think that there is something powerful about the "debugging" you always have turned on in Excel, in that you always know what value a formula has produced, even after it has run. And you can (usually) easily see at what point an error started in your calculations.
For the programmers out there who aren't fans of Excel, or aren't super familiar with it, if you haven't seen "You Suck at Excel with Joel Spolsky" [0] you might be pretty amazed at what you can do with Excel at an intermediate/advanced level.
"to avoid repeating it I'd put that inside another cell"
100%! I like to do that in other cases as well, just to keep formulae simple enough that someone else can easily audit the whole spreadsheet.
I'd rather have 5 extra columns in a calculation, then have a huge formula in a single column. This habit is so strong that I often do the same thing with Pandas: adding extra columns to a dataframe for intermediate calculations, when it would be better to write a larger function and .apply() it all at once.
Yep. Exactly the same reason we break up procedures and functions into smaller units in procedural code, to be able to reason about individual pieces, and understand each step.
I've never used Pandas, but I imagine I'd have the exact same instincts to break calculations up as you do, since Excel was my "first programming language" that I was first exposed to in the 4th grade, haha. I certainly didn't learn much advanced stuff at that point, mostly because of the time period and being in a rural Midwest area there weren't a lot of programmers around to learn from and the internet was rather different in the mid 90's. :) But Excel planted the seed of programming in my mind, even though I didn't know what "programming" was.
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.
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)
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.
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...)
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.
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!
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.
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.
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.
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.
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.
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!
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).
> 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.
Just to throw another anecdote on top of yours, I've never worked in a shop where they weren't grumbled about from time to time - so I personally don't find the parent's claim to be baseless at all.
Many developers struggle with the "language" of regex - and no matter how many times I "learn it", it doesn't change the fact that I have to pull up references every time I'm building out an expression.
Grandparents post re: Excel had me curious actually - because I (personally) find the use of Left, Mid, Right, etc generally far more logical and readable than trying to parse a regex string.
I was under the impression the primary use for ORM isn't because SQL is complicated (it really is not) but rather because it is embedded in strings and therefore un-testable and opaque to the compiler.
And the complexity of SQL to someone who already codes is marginal. Here in Excel we are talking about the complexity to someone with no coding experience.
The motive behind ORM is in the name: it is to Map Objects to Relations.
The promise is that a Java (then Ruby) developer, could simply design the objects needed for the program, and the fields which need to be persistent could be automatically mapped to the database using ORM.
The reality is quite different of course, there's a reason ORM is so widely derided. But ORM is more about skipping the bookkeeping involved in setting up persistence for application code, rather than testability or opacity of SQL.
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.
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.
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?
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._
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.
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.
Macros are not “formulas in Excel”, lambdas are.
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)
Yes it's good but "revolutionize" is misleading.
I don’t think it’s misleading, I mean everyone’s definition of “revolutionary” is different, but let’s put it into perspective: this is adding functions and recursion to one of the worlds most popular data analysis languages.
I think most people on this site know how powerful the concepts of functions and recursion can be!
Functions and recursion is already there, but only with VBA. I'll say revolutionize if VBA wasn't exist.
Adding to what others have replied, the difference is simply put: If you can write your custom function as a lambda instead of a VBA function you will not have to save your Workbook as XLSM (M for Macro). That means no warnings when the user opens it and much better security because the lambdas can only call built-in functions and other lambdas.
To be honest, I think this is awesome and has been sorely missed.
It’s absolutely true. Most spreadsheets in BigCo are viewed by at least 3 or 4 people. No one likes it when they open a spreadsheet from someone else with a macro because it dramatically increases the learning curve.
If you just had custom functions, you can trace them back pretty quickly and end up with an understanding. Teams will also probably create ‘known’ custom functions for their use case, like converting account Chart of Account codes to finance COA codes etc.
It seems obvious to me why "VBA in a cell" is a nonstarter and why making this a formula-level feature is necessary. I'm glad macros have worked for whatever use case in the past, but it can never be a capital-f Feature of excel because it's a security nightmare and microsoft and everyone that uses macros knows this (or should).
If you want a platform to succeed, make it capable of satisfying most users needs within its sandbox; using macros is just giving up and working around it.
Allow me to reminisce.. in 2012 I created a JavaScript VM extension that executes custom functions inside native Excel. The extension also had a corresponding web-based front-end for creating apps that read/write directly to Excel. This was my baby, but investors (ie, YC) didn't think it was disruptive enough. Now I see these apps (ie, AirTable) pop up every month. Should I open source it?
If you open source it you can't sell it. If you couldn't sell it anyway you might be able to make some money supporting it as open source.
I like how Lambda means very different things between AWS and Microsoft.
"Let's just create a Lambda for this"
Ok... But which one?
I don't want to be cynical, but they could have done this with reason. They could have also called it functions or methods. But chose to disrupt search results for a competitor.
Your anti-M$FT bias is showing. AMZN struck first... Microsoft was using the term Lambda for a prominent feature in its flagship language (C#) before AWS ever existed.
Here's a Stack question dated 2008: https://stackoverflow.com/questions/167343/c-sharp-lambda-ex...
Eye for an eye then. :-) For someone claiming I'm anti-MS, funny you use the mocking M$FT abbreviation.
This would be pretty great if available on Azure Stack on prem.
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
> 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
> 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
Totally agree, we even applied to YC 5 years ago with an excel replacement specifically because of this limitation.
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.
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!
Yet here we are. Microsoft is surprising everybody lately.
Actually what might be better is "sheet functions". So like pages where you can define input and output cells. Then you top level sheet can invoke the "function" as like "=myfunctionsheet{A1=5, A2=9}.N8". That way you get local scope, etc, and you can do more complex stuff because it's very visual instead of being a mathematical function.
Obviously this will be best when used with Name Manager (especially if names can be scoped to the sheet) to name the input and output cells, rather than indexing off the cell ids directly.
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.
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.
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.
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?
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.)
"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.
Hmm. A lambda platform that just runs spreadsheets. This is a killer idea.
This is what I thought it was: upload spreadsheet, get API
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/Elast...
Fastest beeline from research lab to the end-user I've ever seen.
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.
=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?
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
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.
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-3484...).
Huh, are you sure? I don’t think you can (currently) define a lambda in a cell without also calling it in that cell (which means the cell now evaluates to the function’s return value, not a function). The LAMBDA docs:
“If you create a LAMBDA function in a cell without also calling it from within the cell, Excel returns a #CALC! error.”
https://support.microsoft.com/en-us/office/lambda-function-b...
It will show #CALC! for that cell because, well, it has to show something for the result of the formula - but the formula didn't evaluate to a "displayable" value.
Yeah that wasn't the best example on my part. But I have tried it and confirmed that calling lambdas defined in another grid cell doesn't work.
Specifically:
1) Put =LAMBDA(x, x+1) in A1
2) Try calling =A1(1) from any other cell - it will return #REF!
I'm on the Office Insiders beta track and LAMBDA does work otherwise.
Yeah, I was expecting you to be able to define a lambda in a cell, so like `B1` could be `=LAMBDA(x => $A1 + x + 1)`. Then you could assign another cell say `D1` to like `$B1($C1)`. Drag-filling B1 down would make say `B2` equal to `=LAMBDA(x => $A2 + x + 2)` or something, so that it would work like people would think Excel Lambdas should.
That would be more along the lines of lambdas in the traditional sense of anonymous functions, closure captures, higher order functions(?), etc. Could be cool, but IDK if it would be useful? Can't think of any specific use cases right now, but perhaps once people get used to it, there would be tons of them.
It allows for exactly that - if B1 defines a lambda, then you can do B1(x) elsewhere.
Can you do LAMBDA(...)(x)? That would make it an anonymous function.
Of course the real fun starts when you can do:
You can, but you can't (currently?) do =A1(x) where A1 is a LAMBDA function. I think LAMBDA(...)(x) is only useful for testing/development, but maybe I'm missing some good use cases.
If need be you could always just define a lisp interpreter.
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.
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...
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.
They also announced the LET expression which helps a bit with the formatting problem - https://techcommunity.microsoft.com/t5/excel-blog/let-names-...
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.
That already works.
Oops, you're right!
I too was surprised! :)
I guess scope is the other thing missing to make it more programming language like. I wonder how you'd do scoping in a spreadsheet.
The disparity between MacOS and Windows on this point is particularly wanting. No scope field. No comment field.
Actually what might be better is sheet functions. So like pages where you can define input and output cells. Then you top level sheet can invoke the "function" as like "=mypage(A1, A2).N8". That way you get local scope, etc, and even excel noobs could figure it out because it's very visual instead of being a mathematical function.
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-b...
(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.
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:
But if only Excel would support regular expressions like Google Sheets [0], could be done as easily as:
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
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.
> 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.
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.
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.
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.
This is extremely significant to simplifying spreadsheets.
Yes, but there is no mention of debugging. In the hands of Excel cowboys, this can become another foot gun.
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.
A REPL treated as a software system.
Between this and LET (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.
I see it making spreadsheets easier to convert into code, too. Many “business programs” begin as a spreadsheet.
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.
Next up: Excel functions running as Azure Functions in the cloud.
I assumed that was what this is!
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.
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.
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.
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.
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.
Here’s hoping that the capability will be available for online excel (which otherwise pales in comparison to the pc client version)!
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."
I hope they add an import and export function to this. Ideally you could sync these functions at an org level via O365.
Then you need version control. Better to let the document be the boundary of consistency.
Or support versioning using GitHub! Excel has always been a powerful repl, these extensions are leveling it up.
This would make Excel 100x more maintainable. I would think a nice Github integration would turn excel closer to a maintainable programming language.
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.
For that type of pattern, you can also use the new LET function:
https://support.microsoft.com/en-us/office/let-function-3484...
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.
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.
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)
And IFERROR() wasn't added until Excel 2007. The other way to make that operation less unwieldy if the expression was complicated, to avoid repeating it I'd put that inside another cell, and that way it would be =IF(ISERROR(C2),0,C2), but IFERROR is a much better solution.
Excel is such a good tool in many ways, and such a bad tool in many ways. Really experienced power users can follow Excel formulas much easier than blocks of imperative code. But the more complicated it gets, the harder it can be to follow it all. But that also goes the same for software, too. I do think that there is something powerful about the "debugging" you always have turned on in Excel, in that you always know what value a formula has produced, even after it has run. And you can (usually) easily see at what point an error started in your calculations.
For the programmers out there who aren't fans of Excel, or aren't super familiar with it, if you haven't seen "You Suck at Excel with Joel Spolsky" [0] you might be pretty amazed at what you can do with Excel at an intermediate/advanced level.
[0] https://www.youtube.com/watch?v=0nbkaYsR94c
"to avoid repeating it I'd put that inside another cell"
100%! I like to do that in other cases as well, just to keep formulae simple enough that someone else can easily audit the whole spreadsheet.
I'd rather have 5 extra columns in a calculation, then have a huge formula in a single column. This habit is so strong that I often do the same thing with Pandas: adding extra columns to a dataframe for intermediate calculations, when it would be better to write a larger function and .apply() it all at once.
Yep. Exactly the same reason we break up procedures and functions into smaller units in procedural code, to be able to reason about individual pieces, and understand each step.
I've never used Pandas, but I imagine I'd have the exact same instincts to break calculations up as you do, since Excel was my "first programming language" that I was first exposed to in the 4th grade, haha. I certainly didn't learn much advanced stuff at that point, mostly because of the time period and being in a rural Midwest area there weren't a lot of programmers around to learn from and the internet was rather different in the mid 90's. :) But Excel planted the seed of programming in my mind, even though I didn't know what "programming" was.
Yes, that was a max function. I was trying to keep it simple for people not familiar with the syntax.
I commonly use a lookup function in that context (vlookup, xlookup, or index/match).
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/
We’re bringing back some of that magic ... and more!
https://monitoro.xyz
(disclaimer, I’m the founder)
That's cool. How does it work with website Ts & Cs, which generally don't like scraping?
Cool, checking it out!
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)
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.
So, who'll be the first to build a Y-combinator in Excel?
Does Google sheets let you do anything like this?
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
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...)
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.
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!
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.
That is literally how Google Sheets started (excel2web allowing lambda functions, hosted externally) before being acquired by Google.
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.
They sort of already had this with the old style pre-VBA Excel macros.
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.
When will the WASM runtime will be available.
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.)
This makes me unreasonably happy. Does anyone know how much time there usually is for Office stuff to go out of beta?
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.
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.
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!
Whilst not native, you can emulate these with a user defined function (UDF).
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).
There’s nothing wrong with a small extract or substitute regex, in my opinion.
> 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.
Just to throw another anecdote on top of yours, I've never worked in a shop where they weren't grumbled about from time to time - so I personally don't find the parent's claim to be baseless at all.
Many developers struggle with the "language" of regex - and no matter how many times I "learn it", it doesn't change the fact that I have to pull up references every time I'm building out an expression.
Grandparents post re: Excel had me curious actually - because I (personally) find the use of Left, Mid, Right, etc generally far more logical and readable than trying to parse a regex string.
Every time I have to use Regex I muddle through various tutorials and try stuff until it seems to work.
A horrible but practical way to use them.
Being able to describe them in an Excel-like fashion and have it spit out a working Regex would be nice.
same developers don't like SQL because it's complicated (or rather they don't understand it) and end up using an ORM mess.
yes still SQL is everywhere
I was under the impression the primary use for ORM isn't because SQL is complicated (it really is not) but rather because it is embedded in strings and therefore un-testable and opaque to the compiler.
And the complexity of SQL to someone who already codes is marginal. Here in Excel we are talking about the complexity to someone with no coding experience.
The motive behind ORM is in the name: it is to Map Objects to Relations.
The promise is that a Java (then Ruby) developer, could simply design the objects needed for the program, and the fields which need to be persistent could be automatically mapped to the database using ORM.
The reality is quite different of course, there's a reason ORM is so widely derided. But ORM is more about skipping the bookkeeping involved in setting up persistence for application code, rather than testability or opacity of SQL.
Its much easier than the legacy garbage that many office users are stuck with.
My guess in my organization (huge, 100k people) we probably have ~50 FTEs who do stupid work solely because of this feature not existing.
Yeah, let's make the moloch even more unmaintainable. Does still have the leap year error to be compatible with Lotus?
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.
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-gara... [2] https://www.microsoft.com/en-us/garage/profiles/functions-tr...
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?
They need to support Python.
With Guido being a Microsoft employee now, that may just happen...
They (MS) have been talking about for a while: https://www.reddit.com/r/Python/comments/7jti46/ms_is_consid... Yet to see anything tangible though.