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.
back
1 comments
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)
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.
Macros are not “formulas in Excel”, lambdas are.
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.