LAMBDA and UDF Screenshot
LAMBDA Formula Screenshot
UDF Formula Screenshot
Overview
Have you ever written a custom function in Excel with a LAMBDA or a User-Defined Function (“UDF”)? They are both great options for building functions that reduce complexity, combine separate functions into one, and allow you to reuse and update useful formulas from all over your workbook (check out this basic example 🖼️).
While they are both great options, there are some key differences you should be aware of as you choose which one to use in your Excel files. In this article, we’ll discuss the pros and cons of LAMBDAs and DAFs and examples of both of them.
LAMBDA functions
LAMBDA is a relatively new function that allows you to define a custom function very similar to how you would set a named range, including one or more optional or required parameters. This simplifies long formulas and combines functions that are often separate, allowing you to use them all over your workbook and update them in one spot when needed. They can be very simple (see this example 🖼️) or quite complex.
Pros
There are several advantages of using LAMDBA to define custom functions, including:
- Like other dynamic array functions, they can spill natively when the formula results in an array.
- There is no need to use macros or understand VBA or other programming languages – leverage the existing Excel functions you know to build them.
- Formulas can be simple and brief or long and complex.
- They work well with other Excel features, like conditional formatting.
- MS Office 365 is required, so they are not backward compatible with older versions of Excel.
- There are no advanced debugging tools for LAMBDA functions like there are with UDFs.
- LAMBDA functions can’t access operating system-level settings like UDFs can.
- LAMBDA functions are not directly useable in macros like UDFs are.
- All your users are on newer versions of Excel (ideally MS Office 365).
- You may want to build a formula that can easily spill across multiple cells.
- Your custom function will be relatively simple and contain just a couple lines of formula text.
- Your custom function will be specific to one workbook and you don’t expect to reuse the custom formula all over many workbooks.
User-defined functions
UDFs have been around in Excel for a long time (at least 25 years – when I first used Excel!). UDFs allow you to build custom functions that are called just like native Excel functions or directly within macros, making them reusable in multiple ways. Much like LAMBDA functions, they can be very simple (see this example 🖼️) or quite complex.
Pros
There are several advantages of using UDFs to define custom functions, including:
- They can be stored in Excel files, add-ins, or your personal macro workbook.
- There are extensive debugging tools available, just like when building macros.
- They are generally backward-compatible to Excel versions back to 2007, and in many cases, even earlier.
- They work really well macros, helping you make macros less complex and by making the UDF-referenced blocks much more reusable.
- They must be saved in .xlsm files and enable macros, which may be seen as a security risk.
- They require using programming skills in Visual Basic for Applications (VBA). Although it’s a relatively easy programming language to learn, it’s still more demanding to learn than standard Excel functions.
- They don’t natively spill across multiple cells like LAMBDA and other dynamic array functions.
- UDFs can sometimes be slower than LAMBDA functions, especially when using a spilling LAMBDA function.
- You have a solid understanding of VBA and using/sharing macro-enabled is an option for your users.
- You might want to use your function with macros or with other UDFs.
- Your custom function will be somewhat complex and debugging will be helpful to troubleshoot.
- You might need to access operating system-level settings.
Summary
Recent Comments