Excel Bits & Pieces

Category: Lambdas

  • Workbook Information Functions (Part 2)

    Workbook Information Functions (Part 2)

    In my last post (Workbook Information Functions (Part 1)) I described three custom LAMBDA() functions which could be used to dynamically get useful information about a referenced Excel file, such as:

    • Worksheet name;
    • Workbook name and file type extension; and
    • file path.

    These three functions are relatively simple, work in very similar ways, and are complimentary. As such, they are a great candidate to demonstrate how a module of LAMBDA() functions can be created and maintained using the Excel Labs Advanced Formula Environment.

    Excel Labs Advanced Formula Environment

    Excel Labs is a free Excel add-in created by the Microsoft Garage Projects team. It can be downloaded from Microsoft AppSource and once installed can be accessed via a new “Excel Labs” button on the Excel Home ribbon.

    The Excel Labs add-in has three elements to it:

    1. Advanced Formula Environment (AFE)
    2. LABS.GENERATIVEAI function
    3. Python Editor

    My preference is to set the AFE as the default feature for Excel Labs so this is what opens automatically when I open Excel Labs. The Python Editor is excellent, but since Excel added a Python editor to standard Excel it is no longer really needed. Similarly, the LABS.GENERATIVEAI() function is fun to play around with, but I rarely ever use it and the page that opens from the Excel Labs add-in is only a setup and settings window so is not needed that often.

    The AFE window provides three tabs within the top green ribbon:

    • Grid
      • Provides a formula editor for the cell that is currently selected.
      • The formula editor is more like a traditional code IDE with auto-completion, syntax suggestions, syntax highlighting, error highlighting, and code commenting.
      • There is also a formula debugger for your standard Excel formulae which shows you the steps in the formula being edited in execution order and the result at each stage.
    • Names
      • Provides a manager and editor for defined names.
      • The defined names are split by type/category into Functions, Ranges, Formulas, and Modules.
    • Modules
      • Provides an IDE and editor for each module of defined names/functions.
      • In this format, each module is shown as a single text file with the defined function that make up the module included in the same text file in a set format.
      • By using a module, users can import multiple functions together as a single block of text or from a single Gist URL.

    LAMBDA() Modules

    In the Excel Labs AFE, modules are presented as a single block of text, similar to a single text file in Notepad, which can use line breaks and tab characters to format the text. Each function defined and stored within the module follows the format of:

    Function_Name = LAMBDA(
  ...
);

    You can choose to either add individual functions to the general Workbook module, in which case they will use the name that they are declared with (e.g. =Function_Name() ), or you can add them to a defined module, in which case the function is used with module name and the function name, e.g. =Module.Function_Name().

    The AFE allows for the use of code commenting which can help in documenting and explaining any custom functions you create. The comments can be made as individual new lines with the “//” characters, they can be added to the end of a line of code again using the “//” characters, or you can create a multi-line block of comments using the “/*” characters at the start of the block and ending with “*/“.

    As can be seen in the image above as well as allowing for code comments the AFE editor will colour different syntax elements of a formula code to help distinguish them when reading and editing a formula. The editor also provides auto-completion suggestions as you type and will highlight any formula errors with red underlining to help in debugging your code as you type.

    When the AFE is used in this way to create or edit a custom function, the contents of the AFE formula editor window are saved within the Excel file that you are using in its fully formatted version including any new lines, indentation, and code comments. When you then save the edited function (using the save button in the Excel Labs ribbon), a further copy of the code for each function is created, where all code comments are removed along with superfluous formatting characters, and this copy is saved as a standard Excel Defined Name. It is the Defined Name version which is then used by the Excel file when the user uses a custom function in a worksheet formula as the code comments are not allowed in a valid defined function.

    As such, it is important that if you are using the AFE to create custom functions that any editing of these functions is performed in the AFE editor window rather than directly in the Excel Name Manager otherwise the version of the function code seen in the AFE may not match that seen in the Name Manager and any changes made to a function in the Name Manager will be over-written by the AFE the next time the module/function is saved.

    A major advantage of using Excel Labs for custom functions is how easily they can be added and shared particularly for whole modules of functions. The user can either copy the full commented code for a function or module of functions and paste this directly into the AFE editor window or download the code from a GitHub Gist URL. This means that a repository of finalised and maintained functions can be hosted online and then users can simply import the required functions/modules for their project from the appropriate Gist URL. Once imported from the URL, the functions and any comments are stored in the Excel file and access to the internet and Gist is no longer required in order to use the function(s) in that Excel file.

    Function Documentation

    An important aspect of the native Excel functions is the readily available documentation. If you are unsure of how an Excel worksheet function works, or what an error code might indicate, a quick Google search will lead you to not only to the official Microsoft documentation, but also a list of third party articles and videos explaining how to use that function and its input parameters.

    This is where any custom functions can fall down if you intend to share them with others or include them in projects that will not be maintained by the function author.

    Potential options for providing documentation for your custom functions include:

    • Name Manager
      • As custom LAMBDA() functions are defined as Names, you can use the Comments property for the name to add descriptive text. This Comment text will be saved with the function within the Workbook and so accessible to all users who access the file.
      • However, there is a limit on the string length that you can save in the Comment property of 255 characters. Additionally, although you can use new lines / line breaks, they will be counted in your character limit and are not displayed as line breaks when accessed so are not ideal for function help documentation.
      • The Comment property does have its place though, as the text in the Comment property for a defined custom function will be displayed when the user choses your function in a formula, and so can be useful brief descriptor of the functions intended use
    • Excel Labs Advanced Formula Environment Modules
      • As discussed above, the Excel Labs AFE allows for extensive and formatted code commenting. This means that you can provide all of the necessary code commenting you might want within the function and module definition code.
      • This code commenting is saved in the Workbook along with the defined function(s) and so is accessible to anyone with a copy of the file. However, it requires use of the Excel Labs add-in to access and needs the user to know how to access the information.
    • Online documentation (e.g. Gist / GitHub)
      • Another option would be hosting documentation for any custom functions in the same place that they are hosted. For example, I have taken to creating individual Gists for each new function or module that is created. This allows for the help documentation to be hosted in the same place that code for the function is hosted.
      • I also use a public GitHub repository to host copies of Excel files which contain copies of my custom functions along with development information and help documentation within the same file.
      • The main issue with these methods is that the documentation is not stored with the function in the Workbook where the functions are being used. Therefore, the user needs to know where to look for the information. Although, pointing the user to a URL is much easier than some other methods, and could be easily incorporated into a defined Name Comment property for example.
    • The .About() Function
      • When I first saw Craig Hatmaker’s take on this problem I was impressed and inspired (see my earlier post in which I talk about Craig Hatmaker’s 5G project). He chose to incorporate the function help information into the function itself — all of the parameters for the function are made optional and if the user omits all inputs (i.e. simply =MyFunction()) then the function outputs an array of help information, telling the user what the function does and what the expected input parameters are.
      • For my own functions, I chose not to take this approach as I wanted to keep the distinction of required and optional parameters, and I felt that including the required extra code for this functionality would make all of the functions overly long, especially as I wanted to include input validation and error handling.
      • Instead, for my function modules I chose to add an extra .About() function to the module which could provide useful information about the module itself as well help documentation about each of the functions in the module.

    About() Function

    For the .About() function, the idea was to use a single optional select_function parameter, which can be used to choose the function in the module to provide information about.

    • If the optional select_function parameter is omitted, then the function returns information about the module including the names of the included functions and an index number for each.
    • If the optional select_function parameter is provided as a number, then the function returns the information for the corresponding module function.
    • If the optional select_function parameter is provided as a text string, then the function will try to match the input string against the list of included function names and return information on the matching module function.

    Below is an example of an About() function implementing the above approach for a module containing three custom functions.

    = LAMBDA(
  // Parameter declaration
  [select_function],
  // Main function body
  LET(
    // Deal with the optional input parameter;
    // determine if the input is omitted, or if a number or text is supplied
    _num, IF( ISOMITTED(select_function), 0,
      IF( ISNUMBER(select_function), INT(select_function), "#")
    ),
    // Create the output array for information about the module as a whole as required
    _arrDef, IF(_num = 0, TEXTSPLIT(
      "About:¬RXL example functions.|" &
      "¬Suggested module name = RXL_Demo|" &
      "Version:¬v1.01  (March 2025)|" &
      "Author:¬R Silk, RXL Development|" &
      "URL:¬https://gist.github.com/...|"&
      "¬|" &
      "Function¬Description|" &
      "0 - About¬Provides information about the functions in this module|" &
      "1 - Demo_One¬Description of the action of the Demo_One function|" &
      "2 - Demo_Two¬Description of the action of the Demo_Two function|" &
      "3 - Demo_Three¬Description of the action of the Demo_Three function",
      "¬", "|"),
      ""
    ),
    // Creates an array of the function names in the module to match user input against
    _arrNames, TEXTSPLIT(
      "Demo_One¦" &
      "Demo_Two¦" &
      "Demo_Three",
      "¦"
    ),
    // Creates the array of function documentation
    _arrHelp, TEXTSPLIT(
      "NAME:¬Demo_One|" &
      "DESCRIPTION:¬Description of the action of the Demo_One function|" &
      "VERSION:¬v1.01  (09-Mar-2025)|" &
      "PARAMETERS:¬|" &
      "range¬(required) A valid cell reference, e.g. 'Sheet1'!A1" &
      "¦" &
      "NAME:¬Demo_Two|" &
      "DESCRIPTION:¬Description of the action of the Demo_Two function|" &
      "VERSION:¬v1.01  (09-Mar-2025)|" &
      "PARAMETERS:¬|" &
      "range¬(required) A valid cell reference, e.g. 'Sheet1'!A1|" &
      "multiplier¬(optional) A number to multiply the sum result by" &
      "¦" &
      "NAME:¬Demo_Three|" &
      "DESCRIPTION:¬Description of the action of the Demo_Three function|" &
      "VERSION:¬v1.01  (09-Mar-2025)|" &
      "PARAMETERS:¬|" &
      "range¬(required) A valid cell reference, e.g. 'Sheet1'!A1|" &
      "multiplier¬(optional) A number to multiply the sum result by|" &
      "as_text¬(optional) Choose whether to output the result as text (True/False)",
      "¦"
    ),
    // Attempts to select the chose function and then 
    // splits the information string into an output array
    _arrOut, IF(_num = "#",
      LET(
        _idx, SUM( SEQUENCE(1, COUNTA(_arrNames)) * (_arrNames = select_function)),
        IF(_idx > 0, TEXTSPLIT( INDEX(_arrHelp, _idx), "¬", "|"), "#[Error: name not recognised]")
      ),
      IF(_num <> 0, IFERROR( TEXTSPLIT( INDEX(_arrHelp, _num), "¬", "|"), "#[Error: index not recognised]"),
      "")
    ),
    // Selects whether to output the general module information (_arrDef)
    // or the selected function information (_arrOut)
    IF(_arrDef = "", _arrOut, _arrDef)
  )
)

    The About() function works by accepting a user input as either an index number or function name as text and then returning an output array of information for the chosen function.

    In order to reduce the number of named variables in the function and to keep the approach flexible/dynamic, the output information is stored as long text string with special characters used to indicate the separation by index (“¦“), new line (“|“), and column (“¬“). The strings are concatenated using the & operator to make the overall writing and editing more user friendly and to make the code more readable.

    First (lines 8-10), the function determines whether a value has been supplied for the optional select_function parameter, and if so whether the value is a number, otherwise it is assumed to be text.

    If no input has been supplied, then the general module information string is converted to a readable output array (_arrDef) using the TEXTSPLIT() function (lines 12-26). This default output provides the user information on the module name, module version, etc., as well as the included function names with a brief description of each module and an index number. The output array follows a simple two column structure to make it easier to read, with a title or topic in the first column and the detail in the second column.

    Next, the function creates an array of the function names (_arrNames) using the TEXTSPLIT() function (lines 28-33), which is used to match a passed function name (where the select_function input is a non-number) and determine the correct function index.

    In lines 35-57, the main function help information is split into individual strings with one string per function using the TEXTSPLIT() function (_arrHelp) and based on the separation between functions using the “¦” character.

    Once the _arrHelp array has been created, the required function index is determined using the _num variable (if a number was supplied) or the _idx variable (if a function name was supplied), and attempts to select the required function information string from _arrHelp using a simple INDEX() function call. This selected function information string is converted to an output array (_arrOut) using the TEXTSPLIT() function (lines 60-67), again following the same two column format, where the “¬” character denotes a new column and the “|” character denotes a new line / new row.

    Finally, the function either returns the created output arrays _arrDef or _arrOut depending on whether or not the user supplied an input value for the optional select_function input parameter (line 70) .

    Workbook Information Module (RXL_WbInfo)

    Below is the final, combined LAMBDA() module which incorporates the About() function, along with the SheetName(), WorkbookName(), and FilePath() functions.

    The functions are shown in outline only, concentrating instead on the format and structure of the comments, documentation, and definitions. (Details on the contents of the module functions can be found above for the About() function and in the Workbook Information Functions (Part 1) post for the other functions).

    /*
    MODULE:     RXL_WbInfo
    AUTHOR:     R Silk, RXL Development
    WEBSITE:    https://excel-bits.net
    GIST URL:   https://gist.github.com/r-silk/9c18090f60ffe442b7d22e6058e83e4a
    VERSION:    1.06 (Apr-2025)
*/
/*
    Name:        About()
    Version:     1.10 (23-Apr-2025) -- R Silk, RXL Development
    Description: *//**Provides information about the functions in this module*//*
    Parameters:
        + select_function   = (optional) the name or index number of the function that user wants information about
    Error codes:
        #[Error: name not recognised]   = the provided name string does not match those in the module
        #[Error: index not recognised]  = the provided index number does not match those in the module
    Notes:
        - if the select_function input is omitted or an index of 0 is provided then a table of information about the module is returned
        - the user can either provide the index of the chosen function as an integer (1+), or the name of the chosen function as a string

*/
About = LAMBDA(
  
  ...
  
);

/*
    Name:        SheetName()
    Version:     1.04 (23-Mar-2025) -- R Silk, RXL Development
    Description: *//**Returns the worksheet name (as displayed in the worksheet tab) for the cell reference provided; Workbook must be saved first*//*
    Parameters:
        + cell_ref      = (required) A valid cell reference, e.g. A1, or 'Sheet1'!A1
    Error codes:
        #[Error: cell reference required]   = the cell_ref parameter requires a valid cell reference
        #[Error: Workbook must be saved]    = the Workbook file must be saved first in order for the function to work correctly
        #[Error reading Sheet name]         = an error occurred getting the filename string or extracting the worksheet name from the filename string
    Notes:
        - The function outputs custom error messages as strings; these will not be flagged by Excel or any add-ins as error cells.
        - Alternatively, the custom error messages can be replaced with native Excel error codes so that the error is flagged in the normal way; 
          to do this:
            -- replace the "#[Error: cell reference required]" string with the #VALUE! (or #REF!) error code
            -- replace the "#[Error: Workbook must be saved]" and "#[Error reading Sheet name]" strings with LAMBDA(0) in order to create a #CALC! error code
*/
SheetName = LAMBDA(
  
  ...
  
);

/*
    Name:        WorkbookName()
    Version:     1.05 (23-Mar-2025) -- R Silk, RXL Development
    Description: *//**Returns the Workbook name, with or without the file extension, for the cell reference provided; Workbook must be saved first*//*
    Parameters:
        + cell_ref          = (required) A valid cell reference, e.g. A1, or 'Sheet1'!A1
        + remove_extension  = (optional) A TRUE/FALSE or 1/0 value to determine whether to remove the file extension from the workbook filename
    Error codes:
        #[Error: cell reference required]   = the cell_ref parameter requires a valid cell reference
        #[Error: Workbook must be saved]    = the Workbook file must be saved first in order for the function to work correctly
        #[Error reading Workbook name]      = an error occurred getting the filename string or extracting the Workbook name from the filename string
    Notes:
        - The function outputs custom error messages as strings; these will not be flagged by Excel or any add-ins as error cells.
        - Alternatively, the custom error messages can be replaced with native Excel error codes so that the error is flagged in the normal way; 
          to do this:
            -- replace the "#[Error: cell reference required]" string with the #VALUE! (or #REF!) error code
            -- replace the "#[Error: Workbook must be saved]" and "#[Error reading Workbook name]" strings with LAMBDA(0) in order to create a #CALC! error code
*/
WorkbookName = LAMBDA(
  
  ...
  
);

/*
    Name:        FilePath()
    Version:     1.04 (23-Mar-2025) -- R Silk, RXL Development
    Description: *//**Returns the Workbook file path, with or without the full file name, for the cell reference provided; Workbook must be saved first*//*
    Parameters:
        + cell_ref          = (required) A valid cell reference, e.g. A1, or 'Sheet1'!A1
        + remove_last_char  = (optional) A TRUE/FALSE or 1/0 value to determine whether to remove the last character in the file path string
        + inc_file_name     = (optional) A TRUE/FALSE or 1/0 value to determine whether to include the full file name with file extension
    Error codes:
        #[Error: cell reference required]   = the cell_ref parameter requires a valid cell reference
        #[Error: invalid optional input]    = either the remove_last_char ot inc_file_name inputs are not a logical (TRUE/FALSE) or numeric value
        #[Error: Workbook must be saved]    = the Workbook file must be saved first in order for the function to work correctly
        #[Error reading File Path]          = an error occurred getting the file name string or extracting the file path from the file name string
        #[Error reading Workbook name]      = an error occurred getting the file name string or extracting the Workbook name from the file name string
    Notes:
        - The function outputs custom error messages as strings; these will not be flagged by Excel or any add-ins as error cells.
        - Alternatively, the custom error messages can be replaced with native Excel error codes so that the error is flagged in the normal way; 
          to do this:
            -- replace the "#[Error: cell reference required]" string with the #VALUE! (or #REF!) error code
            -- replace the "#[Error: invalid optional input]" string with the #VALUE! error code
            -- replace the "#[Error: Workbook must be saved]", "#[Error reading File Path]", and "#[Error reading Sheet name]" strings with LAMBDA(0) in order to create a #CALC! error code
*/
FilePath = LAMBDA(
  
  ...
  
);

    The above shows my current preferred format for these Excel Labs AFE modules, making use of multi-line comments to provide the module and function documentation. Using comments in this way for the function documentation allows for more extensive and more verbose information but is only accessible via the Excel Labs add-in. Therefore, I also like to add in the About() function to provide more easily accessible function information on the worksheet.

    The initial comment block (lines 1-7) provides the information on the module itself including the suggested module name and version number. I then include the About() function (lines 8-26) followed by the other functions in the order in which I have included them in the About() function information strings.

    Each function is declared and defined in the standard Excel Labs AFE way, starting with the function name and ending with a semi-colon:

    Function_Name = LAMBDA( ... );

    Above each function declaration, I use a multi-line comment block to supply the corresponding function documentation (for example see lines 28-44). This will usually contain:

    • function name;
    • function version;
    • description of the functions action/intended use;
    • input parameters, including information on:
      • whether a parameter is required or optional, and
      • the expected input value(s);
    • potential function output error codes and there meaning; and
    • any additional notes on how the function is intended to work.

    Of note, the description portion of the function documentation is held in its own separate and special comment block. This comment block uses a double asterisk in the format:

    /** [Description text] */

    This is important, as this special comment format indicates to the AFE that this text should be used to populate the Comment property of the defined name that stores the function. As described above the Comment property of the defined function is then displayed to the user when selecting a function to use in their formulae.

    Note: The description supplied in the double asterisk comment (“/** … */”) should be a brief description over a single line as there is a 255 character limit for the text stored in the defined name Comment property.

    If you wish to use the RXL_WbInfo module (or any other AFE module) in your Excel projects, this can be easily achieved using the below steps:

    1. Ensure that you have the Microsoft Excel Labs add-in installed, and navigate to the Advanced Formula Environment (AFE) tab
    2. In the AFE choose the Modules section from the green ribbon at the top
    3. Click on the “Import from URL” button in the black ribbon (looks like a cloud with a down arrow)
    1. In the dialogue box that pops up enter the GitHub Gist URL for the RXL_WbInfo module (https://gist.github.com/r-silk/9c18090f60ffe442b7d22e6058e83e4a)
    2. Before clicking the “Import” button, check the box for “Add formulas to a new module?” and add the suggested module name to the text box that appears.

    Once the module has been uploaded, the AFE will save a copy of the full module string complete with code comments in your Excel file, which can then be read via the Excel Labs add-in. The AFE will also save copies of each defined function in the Name Manager as defined names with the specified Comments property, and so you can start using the functions by simply typing =RXL_WbInfo in the formula bar.

  • Workbook Information Functions (Part 1)

    Workbook Information Functions (Part 1)

    In previous financial models and Excel projects I have worked on, the basic template worksheet has often included a formula in cell A1 (or similar) which dynamically returns the worksheet name (as per the text in the worksheet tab) to display as the worksheet title. This means that if the worksheet name is updated, the worksheet title also automatically updates.

    This is achieved using the CELL() function, which can be used to output the worksheet file name in a standardised format:

    C:\Your\File\Path\[File_name.xlsx]Sheet_Name

    The file path is always shown first, followed by the file name with file extension, enclosed within the square brackets (“[ ]”), and finally the worksheet name for the cell reference provided to the CELL() function.

    Excel restricts the use of certain characters in a valid Excel file name and worksheet name, including the “[” and “]” characters. This means that the last “[” and “]” characters in the CELL() file name string can be reliably used to find separate out the worksheet name, file name and file path.

    Note: There are very few restrictions on the characters that can be used in a valid file path with most modern operating systems (especially Mac OS), so you cannot rely on using the first instance of “[” or “]” characters as you would with a standard FIND() function call.

    In a previous post about my development of a TextBetween() function, I showed examples of formulae to extract the file path, file name, and worksheet name from the CELL() function output, particularly using the newer TEXTAFTER() and TEXTBEFORE() functions (see screen shot below). Following this, I thought it might be a fun exercise to build a set of custom functions which a user could use to extract this useful workbook information dynamically and simply.

    The Workbook Information functions module (RXL_WbInfo) is made up of three custom LAMBDA() functions. The rest of this post details how the functions work, and the next post (Workbook Information Functions (Part 2)) will explain how the Excel Labs Advanced Formula Environment (AFE) can be used to create a module of complimentary LAMBDA() functions.

    Please note: As all of the Workbook Information functions use the CELL() function output as described above, the referenced Workbook must be saved first in order for the functions to work correctly.

    All of the functions outlined below have been designed to look and feel like native Excel functions, including required and optional parameters as appropriate, as well including input validation checks, error handling, and custom error messages. The code displayed in this post has been made to be more easily read and understood with new lines, additional spacing, indentation, and code commenting.

    The version of the Workbook Information functions included here use custom error messages rather than standard Excel error codes (“# values”). I felt that as these functions are less likely to be used in any crucial calculations and are more likely to be used mainly for dynamic display of Workbook details on information worksheets or worksheet titles, this was a good opportunity to demonstrate using such custom error messages. For further discussion on the topic of error messages in custom LAMBDA() functions please see my previous post.

    A. SheetName()

    The SheetName() function is the simplest of the three functions with a single input parameter required; a valid cell reference.

    = LAMBDA(
  // Parameter definition
  cell_ref,
  // Main function body/calculations
  LET(
    // Check that input provided is as cell reference, otherwise create a custom error message
    _inpChk, IF( NOT(ISREF(cell_ref)), "#[Error: cell reference required]", ""),
    // Get the filename string using the native CELL() function and the cell_ref argument
    _fileName, CELL("filename", cell_ref),
    // Use the TEXTAFTER() function to look for the final "]" character in the filename string
    // If no matching "]" character is found then return an empty string
    _wsName, IF(_fileName <> "", TEXTAFTER( _fileName, "]", -1, , , "#"), "#[Error: Workbook must be saved]"),
    // Check for an error value or empty string and if found return a custom error message, otherwise return the worksheet name string
    _rtn, IF( OR( ISERROR(_wsName), _wsName = "#"), "#[Error reading Sheet name]", _wsName),
    // Final check to see if there was an input error, and if so return the custom error message
    IF(_inpChk <> "", _inpChk, _rtn)
  )
)

    The function uses a LET() function after the parameter declarations (line 5), to enable staged calculations and additional temporary variables within the main LAMBDA() calculation.

    The first part of the calculations is an input validation check (lines 6-7), which ensures that the cell_ref input provided is a valid cell reference.

    Next the CELL() function is used to get the full file name string output in the _fileName variable (lines 8-9). Then, the TEXTAFTER() function is used to get text after the last “]” character (instance number -1), which based on the expected CELL() function output, will represent the worksheet name for the cell reference provided (lines 10-12).

    If the _fileName variable is a blank string then this suggests that the Workbook for the cell reference provided is not saved, and so we return an appropriate error message.

    The result of the TEXTAFTER() function call (as per the _wsName variable) is checked, and if an error has occurred then an appropriate error message is provided as the _rtn variable otherwise the extracted worksheet name is used (lines 13-14).

    Finally, the function uses an IF() statement to check whether the input validation check has flagged (lines 15-16) in which case the created error message is returned; if not the _rtn variable is returned, having already undergone error handling during the calculation of the _rtn variable value.

    B. WorkbookName()

    As with the SheetName() function, the WorkbookName() function requires a single valid cell reference as the main input parameter (cell_ref) but also includes the remove_extension optional parameter to add additional functionality.

    • If the input is TRUE the file name without file type extension is returned (equivalent to the Workbook name), otherwise the full file name including the file type extension is returned.
    = LAMBDA(
  // Parameter definitions
  cell_ref,
  [remove_extension],
  // Main function body/calculations
  LET(
    // Update the optional input to a default of False if omitted or a non-Boolean/non-numeric value is supplied
    _extChk, AND( NOT(ISLOGICAL(remove_extension)), NOT(ISNUMBER(remove_extension))),
    _fileExt, IF( 
      OR( ISOMITTED(remove_extension), _extChk), 
      FALSE,
      remove_extension
    ),
    // Check that the cell_ref input supplied is a valid cell reference
    _inpChk, IF( 
      NOT(ISREF(cell_ref)), 
      "#[Error: cell reference required]", 
      ""
    ),
    // Get the filename string using the native CELL() function and the cell_ref argument
    _fileName, CELL("filename", cell_ref),
    // Use a combination of the TEXTAFTER() and TEXTBEFORE() functions to get the workbook name from the filename string
    _strName, TEXTBEFORE( TEXTAFTER(_fileName, "[", -1, , , "#"), "]", -1, , , "#"),
    // Remove the file extension from the workbook name string as required
    _wbName, IF(
      _fileExt, 
      TEXTBEFORE(_strName, ".", -1, , , _strName), 
      _strName
    ),
    // Determine whether to return an error message or the workbook name string
    _rtn, IFS(
      _fileName = "", "#[Error: Workbook must be saved]",
      OR( ISERROR(_wbName), _wbName = "#"), "#[Error reading Workbook name]", 
      TRUE, _wbName
    ),
    // Finally determine whether to return an input error message or the return string
    IF(_inpChk <> "", _inpChk, _rtn)
  )
)

    The function uses a LET() function after the parameter declarations (line 6), to enable staged calculations and additional temporary variables within the main LAMBDA() calculation.

    First, the function deals with the optional remove_extension input parameter (lines 7-13) and sets a default value of FALSE if no input value is provided by the user or an invalid input value is provided.

    Next, the function performs input validation checks (lines 14-19) via the _inpChk variable, which check that the cell_ref input provided is a valid cell reference as required by the CELL() function.

    The function uses the CELL() function to get the full file name output for the referenced cell (_fileName variable; lines 20-21), and then a combination of nested TEXTAFTER() and TEXTBEFORE() functions to extract the referenced Workbook file name (_strName variable; lines 22-23). As previously described, the last “[” and “]” characters (delimiter instance -1) are used as for this step as these are invalid characters for the Excel file name and Worksheet name and so will only be seen encompassing the Workbook file name in the expected CELL() function output.

    If the user has chosen to use the remove_extension optional parameter, the _wbName variable will either the return the full text of the _strName variable (including the file extension) or use the TEXTBEFORE() function to return the text up to the last “.” character in the _strName text (lines 24-29).

    The _rtn variable calculations provide error handling for the main calculations (lines 30-35). If the _fileName variable from the CELL() function output is a blank string this suggests that the referenced Workbook is not saved and so an appropriate error message is provided. If either the “[” or “]” characters were not found as expected in the previous steps, then an appropriate, more general error message is provided.

    The final step in the calculations (lines 36-37) checks whether an input check was flagged and returns the created error message, otherwise the error handled _rtn variable value is returned.

    C. FilePath()

    The FilePath() function is the most complex and flexible of the three functions. It again requires a valid cell reference for the main cell_ref input parameter, and provides two optional parameters:

    • remove_las_char = if the input is TRUE, then the full file path is returned without the trailing “\” or “/” character
    • inc_file_name = if the input is TRUE, then the full file path is returned along with the file name including the file type extension
    = LAMBDA(
  // Parameter definitions
  cell_ref,
  [remove_last_char],
  [inc_file_name],
  // Main function body/calculations
  LET(
    // Input checks
    _chrChk, AND( NOT(ISOMITTED(remove_last_char)), NOT(ISLOGICAL(remove_last_char)), NOT(ISNUMBER(remove_last_char))),
    _fnmChk, AND( NOT(ISOMITTED(inc_file_name)), NOT(ISLOGICAL(inc_file_name)), NOT(ISNUMBER(inc_file_name))),
    _refChk, NOT(ISREF(cell_ref)),
    // Dealing with optional parameters, setting default values if omitted
    _lastChr, IF(
      OR( ISOMITTED(remove_last_char), _chrChk),
      FALSE,
      remove_last_char
    ),
    _incFile, IF(
      OR( ISOMITTED(inc_file_name), _fnmChk),
      FALSE,
      inc_file_name
    ),
    // Get the full file path and name
    _full, CELL("filename", cell_ref),
    // Extract the file path only
    _path, TEXTBEFORE(_full, "[", -1, , , "#"),
    _filePath, IF(_lastChr,
      LEFT(_path, LEN(_path) - 1),
      _path
    ),
    // Extract the Workbook name element inc. file extension
    _wbName, TEXTBEFORE( TEXTAFTER(_full, "[", -1, , , "#"), "]", -1, , , "#"),
    // Check for input errors and calculation errors, and set the appropriate error message
    // Note - errors placed in order of importance/reporting as only a single message is returned for multiple errors
    _errChk, IFS(
      _refChk, "#[Error: cell reference required]",
      _full = "", "#[Error: Workbook must be saved]",
      OR(_chrChk, _fnmChk), "#[Error: invalid optional input]",
      OR( ISERROR(_filePath), _filePath = "#"), "#[Error reading File Path]",
      AND( OR( ISERROR(_wbName), _wbName = "#"), _incFile), "#[Error reading Workbook Name]",
      TRUE, ""
    ),
    // Construct the required output string
    _rtn, IF(_incFile,
      _path & _wbName,
      _filePath
    ),
    // Final check - if an error message is present return that, otherwise return the constructed output string
    IF(_errChk <> "", _errChk, _rtn)
  )
)

    The function uses a LET() function after the parameter declarations (line 7), to enable staged calculations and additional temporary variables within the main LAMBDA() calculation.

    First, the function performs input validation checks (lines 8-11) to ensure that any inputs provided are valid. After this the function deals with the optional parameters remove_last_char and inc_file_name, and assigns default values of FALSE if either is omitted by the user (lines 12-22).

    Next, the function uses the CELL() function to get the full file path for the referenced cell (lines 23-26), and then uses the TEXTBEFORE() function to extract the text before the last instance of the “[” character (delimiter instance number -1). Based on the expected output format of the CELL() function, the final character before the last instance of the “[” character will either be a “\” or “/” character, depending on the referenced file location. As there may be circumstances where the user wants to provide the file path as the folder description only, there is the option to remove this trailing character using the remove_last_char parameter (lines 27-30).

    The Workbook name is also extracted from the CELL() function output (_fileName) and stored as the _wbName variable (lines 31-32).

    Next, error handling is performed to check if any of the input validation checks have failed or if an error has occurred during the preceding main calculations (lines 33-42). This is achieved using an IFS() statement, where the more important errors will trigger first and so the single custom error message will be provided in a hierarchical manner – i.e. invalid inputs come before calculation errors in the reporting to the user for debugging purposes.

    After this the required output string is constructed and stored in the _rtn variable (lines 43-47). If the user has chosen to use the inc_file_name optional parameter, then the full file path (including trailing character) is provided (_path variable) with the full file name and file type extension (_wbName variable); otherwise, the adjusted file path string is used (_filePath variable).

    Lastly, an IF() statement is used to check if an appropriate error message was returned from lines 35-42 (_errChk variable), and if so this is returned to the user as the function output; otherwise the calculated string in the _rtn variable is returned (lines 48-49).

    If you want to see further information on the development and documentation of the above three functions then I have provided an Excel file (RXL_WbInfo.xlsx) which you can download and explore at the following GitHub repository:

    https://github.com/r-silk/RXL_Lambdas

    The file contains a page on each of the function with the final versions of the functions as seen above as well as version of the functions which do not use the newer TEXTBEFORE() and TEXTAFTER() functions and instead demonstrate how the same functionality can be achieved with older Excel functions and approaches. There is also a “Demo” worksheet which demonstrates the outputs from each function using the main input parameter combinations.

    The file contains a copy of each function in the Defined Names manager which can be copied into your own file, or alternatively there is a “minimised” version of each function which can be copied and pasted directly into the Name Manager when defining a new name in your own files — please use the suggested function names when using this method:

    • RXL_SheetName()
    • RXL_WorkbookName()
    • RXL_FilePath()

    Finally, there Gist URLs for each of the functions, however, I would encourage you to read the next post (Workbook Information Functions (Part 2)) and use the Gist URL for the RXL_WbInfo module instead if you are intending on adding these functions to an Excel file via the Excel Labs Advanced Formula Environment (AFE).

  • TextBetween Function

    TextBetween Function

    As someone who remembers the challenges of getting just a part of a longer string using the older Excel functions, I was very excited when the new TEXTAFTER() and TEXTBEFORE() functions were introduced to Excel.

    The Old Way

    Previously in Excel, to get all of the text before or after a particular character (or delimiter) you needed to use a combination of the FIND() and LEN() functions within a RIGHT() or LEFT() function. This approach involves finding the numeric position of the chosen delimiter using the FIND() function and then returning the correct number of characters from the chosen side of the delimiter, via the LEN() function, within a RIGHT() function for text after a delimiter, or LEFT() function for text before the delimiter.

    // For text before the delimiter
= LEFT( [text] , FIND( [delimiter], [text] ) – LEN( [delimiter] )) 
// For text after the delimiter
= RIGHT( [text] , LEN( [text] ) – FIND( [delimiter], [text] ))

    To get the text between two different delimiters using the above approaches can get quite complicated and end up with a complex, nested formula which is difficult to read. Equally, using this approach, finding a specific delimiter where there are multiple such delimiters in the source text string is possible but can get very complicated, e.g. finding the last “.” character in a string using FIND() is challenging.

    Fortunately, there is the MID() function for returning the text from a source string starting at a specified character and going for a stated number of characters.

    // For text between two different delimiters
= MID( [text] , FIND( [left_delim] , [text] ) + LEN( [left_delim] ), FIND( [right_delim] , [text] ) - (FIND( [left_delim] , [text] ) + LEN( [left_delim] )))

    For some more real-life examples, we can look at using the CELL() function to dynamically extract the worksheet name, file name, and file path.

    Note: the CELL() function will provide the referenced Workbook filename in the format C:\File\Path\[FileName.xlsx]SheetName

    To get the worksheet name from the CELL() function output, requires getting the text after the last “]” character which we can achieve using the RIGHT() and FIND() functions. To get the Workbook file name we need to the text between the “[“ and “]” characters, which can be achieved using the MID() function. Even for the relatively simple worksheet name this approach requires three calls to the CELL() function.

    This can be improved using the LET() function as shown below so that only a single CELL() function call is needed but us still more complicated and less easy to understand than using the newer TEXTAFTER() and TEXTBEFORE() functions.

    Additionally, the use of the LET() function to define both the source text string (_file) and the delimiters (_d1, _d2), shows how a more generic formula can be created to find the text between delimiters. However, the FIND() function means that only the first instance of each delimiter is used which could lead to unexpected behaviour.

    Note: Valid Excel file names cannot contain the “[” or “]” characters but a valid file path could, which might lead to problems with the formula above as currently written.

    The New Way

    The major advantages of the new TEXTBEFORE() and TEXTAFTER() functions are:

    • the ability to get the full text from either side of a delimiter character(s) with a single function call;
    • the ability to specify a particular instance of a delimiter character(s) rather than stopping at the first instance that is found; and
    • the ability to search from either the start of the string or the end of the string.

    By using the same CELL() based examples above, the improvement in formula simplicity and readability using TEXTBEFORE() and TEXTAFTER() is clear.

    Note: As previously stated, Excel file names and worksheet names cannot use the “[” and “]” characters, but a valid file path can contain these characters. By looking for the last instances of the “[” and “]” characters (or first instances when reading the string in reverse), these will have to be the characters around the file name regardless of their potential presence in the file path.

    In order to extract the file name from the CELL() function output still requires nested functions as there is not new equivalent to the MID() function. However, the nested TEXTAFTER() and TEXTBEFORE() functions are still simpler, and arguably easier to read, than the use of the MID() function with nested FIND() and LEN() functions.

    TextBetween() Function

    Wouldn’t it be easier to have a single function that was just like the TEXTBEFORE() and TEXTAFTER() functions, which took a text string as an input and returned the sub-string (part string) between two specified delimiter character(s)?

    That was the idea behind the TextBetween() function — use the TEXTAFTER() and TEXTBEFORE() functions to re-create a MID() style function but with all of the advantages and user-friendliness of the new TEXT functions.

    The plan for the custom function was relatively simple and straight forward:

    • create a single function call to mimic the MID() function but that can be achieved with three user inputs
      • input text string (source string)
      • start delimiter character(s)
      • end delimiter character(s)
    • maintain the good aspects of the TEXTAFTER() and TEXTBEFORE() functions
      • i.e. the ability to specify an instance number of the delimiters, and the ability to search from the start or end of the input string
    • make the function feel like a native Excel function with optional parameters, as well as adding in input validation, error handling, etc.

    As always seems to be the case when I build a custom worksheet function, “feature creep” soon added extra flexibility but at the cost of increased complexity and more optional parameters for the user.

    The full function is shown below in a more readable version (lines, spacing, and indentation) along with code commenting. 

    = LAMBDA(
  // Parameter definitions
  text,
  [start_delimiter],
  [end_delimiter],
  [start_instance],
  [end_instance],
  [case_sensitive],
  [if_not_found],
  // Main function body/calculations
  LET(
    // Deal with [optional] parameters and set default values  
    _delimA, IF( OR( ISOMITTED(start_delimiter), ISBLANK(start_delimiter)), "", start_delimiter),
    _delimB, IF( OR( ISOMITTED(end_delimiter), ISBLANK(end_delimiter)), "", end_delimiter),
    _instA, IF( OR( ISOMITTED(start_instance), ISBLANK(start_instance)), 1, INT(start_instance)),
    _instB, IF( OR( ISOMITTED(end_instance), ISBLANK(end_instance)), -1, INT(end_instance)),
    _case, IF( ISOMITTED(case_sensitive), 0, IF(case_sensitive, 0, 1)),
    // Additional input checking for the if_not_found parameter in order to provide #N/A result 
    // when no return string is calculated rather than a #VALUE! Result
    _inf, IF(
      OR( ISOMITTED(if_not_found), ISBLANK(if_not_found), if_not_found = "", AND( 
      ISLOGICAL(if_not_found), if_not_found = FALSE)),
      NA(),
      IF( ISLOGICAL(if_not_found), text, if_not_found)
    ),
    _end, IF( ISLOGICAL(if_not_found), if_not_found * 1, 0),
    // Check for invalid input values -- returns value of: 0 = no input errors; >0 = input error(s) 
    // identified this is placed after dealing with optional parameter default values so that an 
    // omitted input does not register an input error
    _inpChk, IF(
      OR(
        NOT(ISNUMBER(_instA)), _instA = 0,
        NOT(ISNUMBER(_instB)), _instB = 0,
        AND( NOT(ISLOGICAL(_case)), NOT(ISNUMBER(_case)))
      ),
      2,
      1
    ),
    // Calculate the output string using TEXTAFTER() and TEXTBEFORE() functions
    _calc, IF( OR( AND(_delimA = "", _delimB = "")),
      text,
      TEXTBEFORE( TEXTAFTER(text, _delimA, _instA, _case, _end, _inf), _delimB, _instB, _case, , _inf)
    ),
    // For the final calculation output, if the an input error check message/code is present return 
    // that, otherwise return the output string
    _rtn, IF( AND( ISERROR(_calc), _end = 1), text, _calc),
    CHOOSE(_inpChk, _rtn, #VALUE!)
  )
)

    The TextBetween() function only has a single required parameter being the input text (line 3). If only this parameter is provided, the function will simply return the input string as it is.

    The optional parameters are shown in the same way as native Excel functions with the parameter names defined within square brackets, e.g. “[optional_parameter]” (see lines 4-9).

    It may seem counter-intuitive to not require the user to enter both the start and end delimiter input parameters, but this was designed so that the user could chose to provide only a start delimiter, only an end delimiter, or both.

    • If only the start_delimiter parameter, is provided, the function will act like the TEXTAFTER() function and return the text after the start_delimiter character(s).
    • If only the end_delimiter parameter is provided, the function will act like the TEXTBEFORE() function and return the text after the end_delimiter character(s).
    • If both the start_delimiter and end_delimiter parameters are provided, the function will act like the MID() function and return the text between the delimiter characters.

    The delimiter instance numbers are optional input parameters. If provided they act in the same way as the delimiter instance inputs in the TEXTAFTER() and TEXTBEFORE() functions:

    • a positive value (+1) will search forwards for the delimiter character(s) from the start of the input text string
    • a negative value (-1) will search in reverse for the delimiter character(s) from the end of the input text string

    Where the delimiter instance values are not used, the start_instance input will default to +1 and the end_instance input will default to -1, meaning that the first instance of the start_delimiter is used and the last instance (or first in reverse) of the end_delimiter is used. In this way, the maximum text is returned from the input text string.

    As is my preferred style, the main function calculations and output (after the parameter declarations), are within a LET() function to allow for staged calculation steps and the use of additional temporary variables (line 11).

    The first part of the function (lines 12-17) deals with the optional input parameters and sets default values if the user has chosen not to use those parameters.

    There is additional checking for the if_not_found parameter (lines 18-25) as this provides additional functionality. This optional input parameter allows the user to set how the function will act if the delimiter character(s) are not found in the input text string.

    • By default, the function will return a #N/A error when the delimiter characters are not found in the input string.
    • If the user provides a FALSE value to the if_not_found input parameter, the function will return a #N/A error if the delimiter characters not found.
    • If the user provides a TRUE value to the if_not_found input parameter, the function will return the original input text string (text) if the delimiter characters are not found.
    • If the user provides a value/text to the if_not_found input parameter (e.g. “(not found)” ) then this value/text is returned by the function in the case that the delimiter characters are not found.

    Next, the function performs some simple input validation (lines 26-36). If these input validation checks fail, then the _inpChk variable is set to a number other than 1.

    After dealing with the optional parameters and input validation checks, the actual calculations are performed within the _calc and _rtn variables (lines 37-43).

    Finally, the function uses a CHOOSE() function to determine the correct output to return (line 44). If the input validation checks are flagged as failed (_inpChk value > 1), then the appropriate error value or error message is returned, otherwise the adjusted, calculated output value is returned (as per the _rtn variable).

    If you want to use the TextBetween() function in your own projects or custom functions, please use one of the options below:

    1. Using Excel Labs Advanced Formula Environment (AFE) download the function using the Gist URL https://gist.github.com/r-silk/b33b16e634e1ff6aa054cd6149964d80
    2. Alternatively, go to the GitHub repository https://github.com/r-silk/RXL_Lambdas and download the file RXL_TextBetween.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_TextBetween
      • the available file contains the documentation for the function as well as some details on development and testing/example usage
  • Error Codes in Custom Functions

    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_numberResult
    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
  • LAMBDA() functions

    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.