Author: robertsilk

Error Codes in Custom Functions
Excel error codes are sadly a part of life when writing formulas and functions, particularly as those formulas and functions get more complicated.

In the post below I hope to discuss:
1. Excel error codes;
2. the principle of error handling and why you should look to implement it in your custom functions;
3. an approach to implementing error handling in LAMBDA() functions; and
4. a custom function you can use to help implement error handling in LAMBDA() functions.
Excel Error Codes

There are a set number of official Excel error codes (19 in total as far as I can tell), known as ‘# values’ as they all start with a ‘#’ character followed by some uppercase letters to indicate the type of error. These are more intuitive for the user to understand than VBA error codes, which are a number with an often vague description, but can still be challenging to interpret.

Some important pros of Excel error codes include:
- consistent and recognisable way to indicate to users that an error has occurred in a calculation
- recognised as a special error object and treated in a unique manner by Excel and VBA
  - therefore, errors can be picked-up/found by Excel (e.g. Find functionality) or by 3rd party add-ins
- errors propagate through other dependent formulas and can be traced back
The potential draw-backs of Excel error codes include:
- they are short text strings and as such are not very descriptive
- there are a limited number of codes, so a single #VALUE! error from a complicated, nested formula can result from any one the of inputs within any of the nested, and dependent functions within a single formula
- one error code resulting from a formula can lead to a different error code resulting from a dependent formula
These potential draw-backs can make debugging an error in a formula quite a difficult and potentially frustrating task.

Error Handling

Error handling is an important concept which allows the code author to monitor for expected or potential errors when code is run and deal with any errors in a controlled manner. This often includes dealing with calculation errors at important stages in the process, as well as user inputs/interaction.

When working with VBA, any unhandled errors can lead to Excel crashing out or to the dreaded VB Editor (VBE) ‘Debug’ window appearing. Although the VBE debug window can be useful when unexpected errors occur during development, it does not look very professional when releasing a finished project to a client and could lead them to worry about integrity and completeness of the rest of the file.

For VBA code it is possible to provide quite extensive error checking at all stages of code execution and you can set professional looking and informative message boxes to appear if the user provides an invalid input, the workbook structure is not as expected, or an error has occurred. This makes the whole project seem much more professional and polished and you can also provide additional information to yourself within the message boxes or an error log which will help when the client calls you wanting advice or a fix.

Error handling with Excel formulas is more complicated as it is not possible to interrupt formula execution with a message box and get the user to provide valid inputs or make a decision about how to proceed correctly.

Most native (in-built) Excel functions will return a single error code when an error is encountered, although this can be more complicated where dynamic arrays are concerned. There is documentation available from Microsoft to help users interpret what may have caused the returned error code for each function, but as there may be multiple causes for the same error code, it can still be difficult to work out what’s causing your error.

Error Handling in LAMBDA() Functions

My approach to error handling LAMBDA() functions depends on whether the function is a custom function that is expected to act like an in-built Excel function, or a one-off LAMBDA() function that is not intended to be re-used or flexible, in which case error handling is probably unnecessary.

For re-usable custom functions, I use a LET() function within the LAMBDA() function immediately after the parameter declarations. This allows for testing the inputs provided by the user against expected data types, a required data range or data value(s). The following Excel functions can be really useful here:
- ISBLANK() – checks if the input is a blank value / blank cell
- ISNUMBER() -checks if the input is a number
- ISNONTEXT() – checks if the input is a data type other than a text string
- ISTEXT() – checks if the input is a text string
- ISREF() – checks is the input is a valid cell reference
- ISLOGICAL() – checks if the input is a TRUE/FALSE (Boolean) value
If an input check test fails then a flag variable (usually _inpChk) within the LET() function is flagged either with an error message or an error number. Having performed the rest of the function calculation the last part of the LET() function will check if the _inpChk has flagged in which case the appropriate error code or error message is returned by the function, otherwise the calculated result is returned (see the two examples below).

Example of input checks returning chosen error codes:
```
= LAMBDA(
  cell_ref,
  multiplier,
  LET(
    _inpChk, IFS(
      NOT(ISREF(cell_ref)), 2,
      OR(NOT(ISNUMBER(cell_ref)), NOT(ISNUMBER(multiplier))), 3,
      TRUE, 1
    ),
    _rtn, cell_ref * multiplier,
    CHOOSE(_inpChk, _rtn, #REF!, #VALUE!)
  )
)
```
Note: For the example above, the IFS() function in lines 5-9 perform the input value checks.

First (line 6), the cell_ref parameter is checked to ensure it is a valid reference; if not, _inpChk is set to a value of 2.

Second (line 7), both the cell_ref and multiplier parameters are checked to ensure they are, or evaluate to, numeric values; if not, _inpChk is set to a value of 3.

Lastly (line 8), if the preceding checks pass, then by default _inpChk is set to a value of 1. The CHOOSE() function at line 11 will return the calculated value if no input checks flagged (i.e. _inpChk was 1), otherwise it will return a #REF! error code if _inpChk was set to 2, or a #VALUE! error code if _inpChk was set to 3.

Note: When directly typing a LAMBDA() function with the #REF! error code in the Name Manager or a cell, there will be no problems. However, the Excel Labs Advanced Formula Environment (AFE) will not save a function which contains a #REF! value directly, so instead you can create a #REF! value if needed using INDIRECT("").

Example of input checks returning custom error messages:
```
= LAMBDA(
  cell_ref,
  multiplier,
  LET(
    _inpChk, IFS(
      NOT(ISREF(cell_ref)), "#[Error: cell_ref must be a valid cell reference]",
      OR(NOT(ISNUMBER(cell_ref)), NOT(ISNUMBER(multiplier))), "#[Error: inputs should be numbers]",
      TRUE, ""
    ),
    _rtn, cell_ref * multiplier,
    IF(_inpChk <> "", _inpChk, _rtn)
  )
)
```
An important consideration is whether to return an Excel error code from your function or a custom error message. It is also useful to consider a hierarchy of errors, i.e. which error is the most important in which case error that is returned above other error types. This will avoid providing a daunting list of errors to the user and encourage the user to fix the more important error first, but also leads to the potentially frustrating scenario of fixing an error, only for a new error code to appear instead.

When deciding whether to use Excel error codes or custom error messages for error handling in your custom functions, it can be useful to consider the following:
- Excel error codes
  - easily recognised by (most) users as an error
  - can be picked-up as error values by Excel and 3rd party add-ins
  - will be treated as native error values by Excel and 3rd party add-ins
  - not very descriptive of reason for the error
  - requires additional documentation to assist user in debugging the error code
- Custom error messages
  - can be as short or long as required and so can be very descriptive as to the reason for the error
  - can create and use a standardised and custom message style for consistency
  - may not require additional documentation to help user debug any errors produced
  - will not be recognised by Excel or 3rd party add-ins as an error value
  - may not be recognised by users as an error value, especially if the function is expected to return a text string
  - longer error messages may be truncated or hidden by surrounding cell data
CErr (Create Error) Function

A flexible method for creating and returning native Excel error codes is to use a custom LAMBDA() function which contains methods for creating the required error code based on an input number.
```
= LAMBDA(
    // Parameter definitions
    error_number,
    // Main function body/calculations
    LET(
        _err, error_number,
        // Input value checks
        _inpChk, IF(
            // Check that a numeric value has been provided as the function input
            NOT(ISNUMBER(_err)), "#[Error: invalid input]", 
            // Check that the number provided is within the required range (1-to-14) and is a round/integer value
            IF(
                OR(_err < 1, AND(_err > 14, _err <> 19), MOD(_err, 1) <> 0), "#[Error: invalid input]",
                // If no input value concerns, return an empty string
                ""
            )
        ),
        // Calculate the return value
        _rtn, IF(
            // Attempt to match the provided number to the hardcoded list of supported error numbers
            ISERROR( MATCH(_err, {1,2,3,4,5,6,7,8,13,14}, 0)), "#[Not supported]",
            // If the match is successful return/create the correct error code
            CHOOSE(_err, #NULL!, #DIV/0!, #VALUE!, INDIRECT(""), #NAME?, #NUM!, #N/A, #GETTING_DATA, , , , , LAMBDA(0).a, LAMBDA(0))
        ),
        // Check if an input error message has occurred otherwise return the  error code/message
        IF(_inpChk <> "", _inpChk, _rtn)
    )
)
```
There are 19 Excel error codes that I am aware of, however the following are not supported by the CErr() function:
1. #SPILL! = not supported as this error relates to worksheet structure and array dimension issue rather than a function calculation issue
2. #CONNECT! = not supported due to difficulties with re-producing this error in a robust and repeatable manner
3. #BLOCKED! = not supported due to difficulties with re-producing this error in a robust and repeatable manner
4. #UNKNOWN! = not supported due to difficulties with re-producing this error in a robust and repeatable manner
1. #PYTHON! = not supported as this error is intended to relate only to errors from the PY() function; therefore could cause confusion if used with a non-PY() worksheet function
The CErr() function produces the following output:

error_number Result
1 #NULL!
2 #DIV/0!
3 #VALUE!
4 #REF!
5 #NAME?
6 #NUM!
7 #N/A
8 #GETTING_DATA
13 #FIELD!
14 #CALC!

As the CErr() function is designed to produce an Excel error code/value, any function errors (i.e. an invalid input value) will result in a custom error message so that it is clear that the function is not working correctly.

If you want to use the CErr() function in your own projects or custom functions, please use one of the below options:
1. Using Excel Labs Advanced Formula Environment (AFE) download the function using the Gist URL https://gist.github.com/r-silk/8ca0742d549ec4153d403b0a64847974
2. Alternatively, go to the GitHub repository https://github.com/r-silk/RXL_Lambdas and download the file RXL_CErr.xlsx
  - this file contains a copy of the function in the Defined Names which can be copied into your own file
  - there is also a “minimised” version of the function which can be copied and pasted directly into the Name Manager when defining a new name of your own — please use the suggested function name of RXL_CErr
  - the available file contains the documentation for the function as well as some details on development and testing/example usage
16th Feb 2025
LAMBDA() functions
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.
9th Feb 2025