Lambda: Turn Excel formulas into custom functions • Hacker News Board

Re: Lambda: Turn Excel formulas into custom functions

#61

Post by layer8 » Sun, Dec 06, 2020, 8:58 PM UTC

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…

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.

Re: Lambda: Turn Excel formulas into custom functions

#62

Post by ripley12 » Sun, Dec 06, 2020, 9:00 PM UTC

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?

Re: Lambda: Turn Excel formulas into custom functions

#63

Post by samfisher83 » Sun, Dec 06, 2020, 9:03 PM UTC

layer8 wrote: post #61

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…

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.

Re: Lambda: Turn Excel formulas into custom functions

#64

Post by layer8 » Sun, Dec 06, 2020, 9:09 PM UTC

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.

Macros are not “formulas in Excel”, lambdas are.

Re: Lambda: Turn Excel formulas into custom functions

#65

Post by askvictor » Sun, Dec 06, 2020, 9:16 PM UTC

ehejsbbejsk wrote: post #44

They need to support Python.

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.

Re: Lambda: Turn Excel formulas into custom functions

#66

Post by omneity » Sun, Dec 06, 2020, 9:17 PM UTC

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)

Re: Lambda: Turn Excel formulas into custom functions

#67

Post by askvictor » Sun, Dec 06, 2020, 9:18 PM UTC

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)

Re: Lambda: Turn Excel formulas into custom functions

#68

Post by MegaDeKay » Sun, Dec 06, 2020, 9:25 PM UTC

askvictor wrote: post #67

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.

Re: Lambda: Turn Excel formulas into custom functions

#69

Post by rrjjww » Sun, Dec 06, 2020, 9:26 PM UTC

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.

Re: Lambda: Turn Excel formulas into custom functions

#70

Post by MegaDeKay » Sun, Dec 06, 2020, 9:29 PM UTC

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?

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