Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

Lambda: Turn Excel formulas into custom functions

techcommunity.microsoft.com

Post reply on HN
Print view View original

71–80 of 140 posts

Re: Lambda: Turn Excel formulas into custom functions

#71

Post by Closi »

samfisher83 wrote: post #63
layer8 wrote: post #61

Earlier quoted context omitted.

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.

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)

Re: Lambda: Turn Excel formulas into custom functions

#72

Post by daxfohl »

ripley12 wrote: post #62
daxfohl wrote: post #55

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?

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.

Re: Lambda: Turn Excel formulas into custom functions

#73

Post by [deleted] »

ripley12 wrote: post #62
daxfohl wrote: post #55

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?

[deleted]

Re: Lambda: Turn Excel formulas into custom functions

#74

Post by bombcar »

kaishiro wrote: post #52
craftinator wrote: post #45

Earlier quoted context omitted.

> 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…

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…

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.

Re: Lambda: Turn Excel formulas into custom functions

#75

Post by tzm »

jtsuken wrote: post #59

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 d…

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?

Re: Lambda: Turn Excel formulas into custom functions

#77

Post by MegaDeKay »

CrazyCatDog wrote: post #26

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

Re: Lambda: Turn Excel formulas into custom functions

#78

Post by samatman »

cm2187 wrote: post #58
somurzakov wrote: post #49

Earlier quoted context omitted.

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.

Re: Lambda: Turn Excel formulas into custom functions

#79

Post by MegaDeKay »

croes wrote: post #16

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

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?

Re: Lambda: Turn Excel formulas into custom functions

#80

Post by robertlagrant »

omneity wrote: post #66
orliesaurus wrote: post #46

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-…

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?
Post reply on HN