3 ms·

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(

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.

layer8 6y ago
Macros are not “formulas in Excel”, lambdas are.
Closi 6y ago
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)
- fomine3 6y ago
  Yes it's good but "revolutionize" is misleading.
  - Closi 6y ago
    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!
    - fomine3 6y ago
      Functions and recursion is already there, but only with VBA. I'll say revolutionize if VBA wasn't exist.
slowwriter 6y ago
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.
wtvanhest 6y ago
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.
infogulch 6y ago
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.

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.

layer8 6y ago

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

Closi 6y ago

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)

fomine3 6y ago
Yes it's good but "revolutionize" is misleading.
- Closi 6y ago
  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!
  - fomine3 6y ago
    Functions and recursion is already there, but only with VBA. I'll say revolutionize if VBA wasn't exist.

slowwriter 6y ago

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.

wtvanhest 6y ago

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.

infogulch 6y ago

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.