LAMBDA: CBSE Template
CBSE-Compliant LAMBDA Source Template
CBSE (Component-Based Software Engineering) compliant LAMBDAs should emulate Excel's functions to achieve the same level of usability by non-technical users (component consumers). This source template strives to meet that goal, as best we can, by including sections that address the following characteristics of Excel's functions:
The function has a meaningful name that non-technical users can understand readily.
The function includes user help.
Absolutely no modification of the source is needed to use the function.
The function can be used in any workbook in which it has been loaded.
Optional arguments with defaults are allowed.
The function provides user feedback when any argument is invalid or the function returns an error.
Structure
The basic template structure is:
Comment Block
Help
Check Inputs
Main Procedure
Error Handling
Return Results
Comments
LAMBDA source has three different comment types:
/* Starts a multiline comment. */ Ends it
/** Starts a comment that will be added to the comment box in Name Manager. */ Ends it.
// Starts a single line comment. Carriage return (new paragraph) ends it.
Terms
Module - LAMBDA sources grouped together, hopefully, because they are related in some way.
Arguments - Synonymous with Parameters
Parameters - Synonymous with Arguments
#1: Meaningful Name
Coming up with a short, meaningful, and easily understandable name can be challenging. Spend time on this because we cannot change the name without breaking backward compatibility. I like to append the LAMBDA symbol (λ - inspired by Peter Bartholomew) to my names so users know my LAMBDAs are not native Excel functions. I place the name in the comment block and, of course, it will also show in the first line of the actual code.
Duplicating Names
Names must be unique within a module. To prevent names in one module from duplicating names in another module, modules can be given names which become prefixes to the LAMBDA's name. In this example, SumRowsλ() will become BXR.SumRowsλ() if, the module it is in is named BXR.
LAMBDA Names are locally scoped to their module
If a LAMBDA requires another LAMBDA within the same module, it can be referenced without the module's name. Thus, SumRowsλ() could be called from another LAMBDA within SumRowsλ()'s module without the BXR prefix.
Version
Excel's function versions are tied to their Excel version. This is not true for LAMBDAs so we must do something different. I recommend including a date published within the comment block and Help text.
Why Versions?
Components may be updated to fix problems, add features, improve efficiency, improve readability, etc. With each change we should record the date of the change, who made the change and a brief explanation of the change. This can help users determine if they have the latest version and if they need to update their copy.
#2 Help
Excel's functions have help which I find immensely useful when first encountering a function. We can add help to LAMBDAs and have LAMBDAs return that help when no arguments are entered, an argument is incorrect, or the LAMBDA fails.
NOTE! I want users to have the option to enter the function with no parameters to display Help. To accommodate that objective, all parameters MUST, as far as LAMBDA is concerned, be optional. Square brackets around a parameter indicates it is optional. To make some 'optional' parameters required, I check if they are omitted, and if so, raise an error. See #6 Error Handling
Help's structure is:
DESCRIPTION: This is a very brief description of the LAMBDA's functionality.
VERSION: This is the date this LAMBDA was published
PARAMETERS: These include all of the function's arguments, their name, whether required or optional, and a brief description.
EXAMPLES: This infers the function's syntax and provides example usage to help users understand what the function is and how to use it.
What are those odd characters?
I use TEXTSPLIT() to create my help text. This places each line in its own row with two columns. The first column is the section name. The second column is the description. TEXTSPLIT() requires that we identify which character designates a new line and which designates a new column. I wanted to use characters that are not likely to be part of a description. That ruled out commas and semi-colons. I also wanted characters that implied what they are for, so I settled on the right Arrow character (→ ALT 26) for a new column and the paragraph character (¶ ALT 0182) for a new row.
#3 No Modifications
Like Excel's functions, CBSE functions must be "use as is." To achieve that goal, CBSE-compliant LAMBDAs must not have any external references. This means no cell references, no named ranges, and no structured references. Everything the function needs is self contained with the exception of the function's parameters.
#4 Use in Any Workbook
Like Excel's functions, CBSE functions must be usable everywhere and permit multiple instances within the same workbook. This assumes the Excel application supports all features leveraged by the CBSE-compliant LAMBDA.
#5 Optional Arguments
Like some Excel functions, CBSE functions can support optional arguments and provide defaults. To achieve that objective I check all optional parameters using ISOMITTED and testing if they are a null string. If that is the case, I code a default, otherwise, i return the parameter. Here is a snippet checking an optional parameter and return true if it is omitted or a null string.
// Check Inputs
SortCodes, IF(OR(ISOMITTED(SortCodes), SortCodes=""), TRUE, SortCodes),
#6 Error Handling
Excel's functions return codes like #VALUE!, #DIV/0! etc. Personally, I find them minimally helpful. We can return those codes with LAMBDA too. In my experience, the Help seems to have all that is needed to figure out what is wrong and how to fix it which makes Help the ultimate error message.
In this template, we see the last step in the function choosing to return the function's Result if there are no errors, or its Help if the are errors. Returning results can get tricky with arrays. For example, =IFERROR(Result, Help) works under some conditions and under other conditions, can convert a one row result into a result with as many rows as rows in help. I found the method I'm using to be safe in all situations I have tested. No matter how many rows or columns are in my result, this method always returns the Result and Help as intended. The reason why it is always safe is that it insures that Error will be a single value of 1 or 2.
// Handle Error
Error, MAX(IsError(Result)+1),
// Return Result
Choose(Error, Result, Help)
Checking Parameters
To trigger displaying help when required parameters are missing I place errors like #VALUE! into invalid parameters like so.
// Check Inputs
Values, IF(OR(ISOMITTED(Values), Values=""), #VALUE!, Values),
GroupColumn, IF(OR(ISOMITTED(GroupColumn), GroupColumn=""), #VALUE!, GroupColumn),
An error in a parameter raises an error in the result (assuming we haven't use some error ignoring option like IFERROR() in the main function).
