When I first came across the LAMBDA() function in Excel I was a little underwhelmed. Don’t get me wrong, Excel Lambda functions are great but my experience of them at this time was limited.

When I started using the LAMBDA() function, my only experience was using it within dynamic array helper functions like BYROW(), BYCOL(), SCAN(), etc.

At the time I was working on developing a financial model based solely on dynamic array calculations and functions. Through this I really saw the utility of functions like BYCOL(), SCAN() and REDUCE() and had got very used writing short LAMBDA() functions as part of these dynamic array helper functions (this was before the joys of eta reduced lambdas). During this time I learnt about being able to define LAMBDA() functions as defined names and thereby re-use a function, which after writing the same short LAMBDA() functions over and over again seemed like an absolutely brilliant idea!

The inspiration

The moment that I came to truly love LAMBDA() functions was when I came across a talk by the mighty Craig Hatmaker, where he discussed his own Lambda functions and his 5G concept for financial model builds. He demonstrated creating modules of brilliant Lambda functions complete with code commenting and some in built documentation using the Excel Labs Advanced Formula Environment (AFE). In particular I liked his approach of using a standardised function structure and the use of the LET() function within the Lambdas.

These Lambda modules by Craig Hatmaker are what inspired me to start building my own Lambda functions and modules in a structured and standardised way, to try and make my functions as professional, flexible and robust as possible.

My approach

Below is an outline of my preferred approach to building LAMBDA() functions which can be easily re-used and shared with others, and hopefully more closely mirror the native Excel functions both in style and behaviour to make them more user friendly.

Please note: At some point I will no doubt change my mind about the best way to structure Lambdas functions and change my mind about everything below. When that happens I’ll try to write a new post about why I was so wrong before, but until then this is my preferred approach.

= LAMBDA(
  // Define the function parameters
  parameter_one,
  [parameter_two],
  // The main function code is enclosed within a LET() function
  LET(
    // Start by dealing with optional parameters
    _paraTwo, IF( ISOMITTED(parameter_two), "", parameter_two),
    // Input checks
    _paraOne, parameter_one,
    _inpChk, IF( OR( ISNUMBER(_paraOne), ISNUMBER(_paraTwo)), #VALUE!, ""),
    // Perform the main function calculations
    _valOne, IF( 
      ISLOGICAL(_paraOne),
      IF(_paraOne, "Hello World!", "Goodnight New York!"),
      _paraOne
    ),
    // Finish the main function calculations with a variable called _rtn
    _rtn, IF(_paraTwo = "", _valOne, _paraTwo),
    // Check if the input checks have flagged an error
    // -> if there was an input error return that otherwise return the calculated output
    IF(_inpChk <> "", _inpChk, _rtn)
  )
)

The first thing to note is the use of space, indentation, and new lines. In my opinion this makes the code much easier to read and follow, allowing you to break-out and show some of the structure and logic of your calculations.

Where possible I use code comments to help any user understand what each section of the function is doing, but this can only be included in a documentation/display version of the Lambda or in an Advanced Formula Environment (AFE) version and has to be removed beforing defining the function in the Name Manager if you are not using AFE.

I always use a LET() function after the parameter definitions to enclose the main function calculations and output. This allows for breaking more complex operations to be broken into smaller chunks and allows for temporary variables to be used within the function.

1. Function parameters

When defining parameter names for the function I like to follow a similar style to the parameter names seen in the native Excel functions. I try to use longer more descriptive names in lower case with spaces replaced by underscores. This makes it easier for the user to understand what inputs are expected for each parameter and is more user friendly as it mimics a style the user expects. Parameters are either defined as required or as optional (achieved by placing enclosing the parameter name in square brackets).

Having defined my parameters with longer, more descriptive names, I usually shorten them by re-defining new variables within the LET() function. As a naming convention, I always start these variables with an underscore so that I know it is a variable I have defined/created, and then use standard Camel case usually with an abbreviated version of the original function parameter name.

It is possible to for all function parameters to be optional, which allows the user to call your Lambda without sending any inputs to it. In Craig Hatmaker’s 5G approach this is used as an opportunity to return some function help documentation to the user as a string array (an inspired idea).

My personal preference is to keep any important inputs as required parameters, similar to the native Excel functions, and then provide a separate About() function in any modules to give the user on-the-fly help and documentation.

2. Optional parameters

Having defined the function parameters and opened the main LET() function, I start by dealing with any optional function parameters I may have offered the user.

Here the ISOMITTED() function is key. With the simple line:

IF( ISOMITTED({optional_parameter}), {default_value}, {original_value})

you can easily set a default value for your optional parameter thereby not needing to adjust your main calculations to deal with optional function parameters.

I tend to do this first so that the following input checks will only flag a problem with a value for optional parameter that the user has provided rather than my default/fall-back value (hopefully I have been sensible enough to use a default value which won’t break my main code or lead to my input checks flagging!)

3. Input checks

Having dealt with default values for optional parameters I try to handle potential errors that could occur due to incorrect or unexpected inputs provided by the user.

An erroneous input argument can have an unpredictable impact on the results of your functions main calculations so it is best to try and identify them early and then warn the user. The output from input checks could be a single Excel error code (# value), a single custom error message (string), or even an array of error codes of messages.

My preference is to output a single error code or message to the user, with the most important input error taking preference, as this is the behaviour seen with native Excel functions. The advantage of this approach is that you highlight a single, important error for the user to deal with in a focussed manner; the disadvantage being that as the user fixes one issue, a new, less vital error is uncovered, which can be frustrating for a user.

Whichever approach you choose, it is better to handle an input error early and deal with it in a controlled way where you choose the error code/message, rather than checking for an error at the end of the main function calculations and trying to guess as to the cause or simply allowing the Excel evaluated error code to be returned.

4. Main calculations

For the main function calculations, I like to take full advantage of the LET() function and split the calculations into logical blocks or components.

This method is helpful during development as you can output and test the result at each intermediate stage of the calculations by having the LET() function return the intermediate variable. It is also helps in user understanding and maintainability of the code by avoiding large, complex, nested calculation blocks.

This point in the function structure is also a great time to use indentation and spacing in the function code to highlight the calculation blocks, the calculation logic, and any hierarchy in nesting as appropriate.

5. Calculation output

I finish each of my function calculations with a final _rtn variable. This indicates that this is the intended output (or return) of the main function calculations and signals the end of the main calculations. By following this standard approach it easy to quickly find the output of the function and then work back as needed to see how it is calculated.

It also helps to define the intended output as a variable during development as this makes it easy to change the actual output of your LET() and thereby LAMBDA() function to a different variable within the LET() to do some sense checking and debugging, then finally set the output back to the _rtn variable when finished.

6. Function output

The true utility of setting the intended function output to be the _rtn variable is that I can then use a final step in my output to control what is returned to the user — my last argument of the LET() function uses an IF() statement to decide whether to return an input check flag or the calculated output to the user.

This is helpful as it allows me to control any error messages more easily and avoid unexpected behaviour in the calculations. It is possible to provide an unexpected input to a function parameter and have the calculations evaluate to an answer which the user sees rather than error. It also allows you as the developer to decide what error message the user gets for an incorrect input rather than whichever Excel error code is produced.

By performing input checks at the start of the function and then flagging this at the end of the function, you can even produce custom error messages as strings which the user will see as the output. In my opinion there are pros and cons to using custom error messages which I will talk about in a future post.

You can also extend this approach and have your final LET() argument check for errors flagged in your calculations and return a custom error code or error message to the user, but personally I normally limit myself to handling input error explicitly.

Conclusion

Hopefully the above makes sense and gives you some ideas of how to standardise an approach to writing and developing Lambda functions. I look forward to continuing to tweak my approach and developing my own best practices over time but I feel like this is good place to start.