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

91–100 of 140 posts

Re: Lambda: Turn Excel formulas into custom functions

#91

Post by infogulch »

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.

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.

Re: Lambda: Turn Excel formulas into custom functions

#92

Post by orliesaurus »

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)

Cool, checking it out!

Re: Lambda: Turn Excel formulas into custom functions

#93

Post by daxfohl »

zupa-hu wrote: post #84

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

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.

Re: Lambda: Turn Excel formulas into custom functions

#94

Post by zupa-hu »

daxfohl wrote: post #93
zupa-hu wrote: post #84

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

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.

Re: Lambda: Turn Excel formulas into custom functions

#95

Post by bernardv »

bencollier49 wrote: post #54

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…

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.

Re: Lambda: Turn Excel formulas into custom functions

#96

Post by daxfohl »

zupa-hu wrote: post #94
daxfohl wrote: post #93

Earlier quoted context omitted.

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!

Re: Lambda: Turn Excel formulas into custom functions

#97

Post by zupa-hu »

interblag wrote: post #90
zupa-hu wrote: post #84

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

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

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.

Re: Lambda: Turn Excel formulas into custom functions

#98

Post by layer8 »

bencollier49 wrote: post #54

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…

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.

Re: Lambda: Turn Excel formulas into custom functions

#100

Post by layer8 »

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

Post reply on HN