Forum Discussion
Formula Help
I would have used "Script" and "Macro" as interchangeable terms. Since I know how to do neither (I did a couple decades ago know how to write visual Basic, I think it was) and for the sake of other relative novices, what's the difference, and under what circumstances is each "favored"?
So I recently did a short tips & tricks class on the differences and comparing Macros (VBA), Scripts, and LAMBDA functions. I know YOU know LAMBDA very well but I include it here for others. So in a nutshell:
Macros are based in VBA language, built into Excel, are extremely powerful and can run autonomously in the background (monitor for sheet changes and such) or be executed (e.g. can be run using a button) but only work in desktop Excel and must have security permissions turned on.
Scripts are the newer way you execute more complicated functionality, but limited compared to VBA (e.g. you can't access the OS and other files). It runs in the cloud, can only be executed (e.g. via a button on the sheet), it can be slow/delayed due to it being in the cloud, but it works in basically all versions of excel including desktop app and in the browser.
LAMBDA functions are excellent options for replacing many of the old UDF (user defined functions) that were in VBA. As long as the function can be written using standard in cell functions, you can embed it in a LAMBDA function in the Name Space and be able to call it anywhere in the sheet.
Here is a +/- sheet I created for them:
VBA details:
+ Very powerful
+ On-click, on-change, on-close, on-open, etc…
+ OS and filesystem manipulation
+ Change actual values
+ Change formatting
+ Change workbook settings
- turned off by default
- only supported in desktop (not online, not mobile)
- some workplace filters/rules prevent
MS scripts
- Only run on action (i.e. on button click)
- Run in the cloud (but could be a +)
+ Compatible on all platforms
+ Change formatting
+ Change actual values
+ Change some workbook settings
- No OS/filesystem access
- Script language very different than VBA
- Technically NOT a UDF since can't be in-cell
LAMBDA
+ Built into excel so more efficient
+ Uses familiar excel 'programming' context
+ Auto updates results
- Can NOT change values (only display new result)
- Can NOT change formatting
- Can NOT change workbook settings
- Can NOT access file system (except limited information through use of INFO and CELL functions which are not supported in all platforms)
- Limited calculation to workbook functionality (limited arrays, looping, etc…)
+ Recursion IS allowed
So when would I recommend one vs another?
LAMBDA - I would recommend using this any time it is possible to achieve what you want/need.
MACRO/VBA - I would ONLY use this if it is something only I or the intended user, or other very limited target would be using and understanding that it will only work using desktop app and I can't achieve the needed output/functionality using LAMBDA or Scripts.
SCRIPTS - This gives you the ability to actually make changes to the sheet/values/formating in a prescribed way/function. For example you always import data and then have to do X,Y,Z to format it the way you like, a script might be great for that. Scripts can also be link to and executed by Microsoft Flows so you can have either other actions in the Sharepoint universe trigger an action or just have it as a scheduled event. For example I have a flow that will run a script on an active workbook each day to do some checks for some common errors and then return any potential errors to the flow, which will then email me what those errors are and I can hop into the sheet to make correction and/or notify the user that made the errors and ask them to fix it.
I hope that helps :)