5G: Validating Function Arguments
Description
This class is about identifying errors and providing helpful information to component consumers (end users).
Learning Objectives
At the end of this course, learners will know:
Why it is important to validate function arguments
How to validate function arguments
How to inform users when things go wrong and help them remedy problems.
Classes
Introduction to Component Based Software Engineering
Introduction to LAMBDA Source Structure
Making LAMBDAs "Self-Contained"
Documenting LAMBDAs
Commenting LAMBDA Source
Providing Inline Documentation
Validating Function Arguments
Using LET() to Improve Readability and thus, Maintainability
Testing Components
Certifying Components
Distributing Components and Version Control
Discussion
Excel has a few ways of reporting problems with functions. None are very helpful.
There's a problem with this formula
The dialog at right appears when we enter a formula that Excel cannot understand. For that situation, this dialog is reasonable.
This dialog also appears when we enter an Excel function without any arguments for functions that require arguments. 5G handles the no-argument situation with inline help (below) which is much more helpful. The content of this inline help mirrors the content of 5G's inline help mirrors the content of Excel Online's function argument tooltip.
Excel's response to no-arguments-entered
5G's inline help
Excel Online's function argument tooltip.
You've entered too few arguments
This dialog appears when we enter an Excel function with fewer arguments than required. In a moment we will see how 5G handles the missing-required-arguments situation which is much more helpful.
#VALUE!, #REF!, #DIV/0!, #NAME?, #NULL!, #N/A
Excel displays these codes when arguments are invalid or functions return invalid results. For many Excel users, these codes are cryptic and confusing. 5G provides an alternative to error codes which are error messages. In a moment we will see how to enable these.
Excel's response to some-required-arguments missing
Error Codes vs Error Messages
Things will go wrong. When Excel’s native functions result in errors, they throw one of Excel’s error codes: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, and #VALUE!. LAMBDAs can throw these codes too or we can program LAMBDAs to provide more descriptive and prescriptive error messages.
The Case for Error Codes
Some modelers check for error codes using IFERROR(), ISERR(), or ISERROR(). If those functions detect an error, the downstream formulas may work differently or alert the user that the model has failed. If maintaining familiarity with these modelers is more important, then throwing error codes is probably best.
The Case for Error Messages
I believe the main reason for using error messages is they can and should be more descriptive than cryptic error codes. When things go wrong, our users are already frustrated. We can help ease that frustration by providing a more tailored message such as “The useful life of an asset must be greater than 0.“
Error messages can also be prescriptive by offering suggestions or a call to action such as, “Change the asset’s useful life argument to more than one year.”
The Case Against Error Messages
Error messages do not overtly state they are errors so using IFERROR(), ISERR(), or ISERROR() in downstream formulas will not work.
The Case Against Error Codes
There is a debate in the modeling community about using IFERROR(), ISERR(), or ISERROR() because they respond identically to any error which opens the door to ignoring errors rather than addressing them. Additionally, best practices require an Integrity Checks worksheet for every model. Integrity checks can detect things that do not generate error codes such as balances that do not balance and allocation percentages that do not add up to 100%. Integrity checks can also alert us to KPIs and financial ratios that do not meet thresholds and do not generate error codes. For logic errors, the type that would generate error codes, the Integrity Checks worksheet can use Excel’s ISBLANK(), ISEVEN(), ISFORMULA(), ISLOGICAL(), ISOMITTED(), ISNONTEXT(), ISNUMBER(), ISODD() and ISTEXT() to test for specific logic problems.
The Verdict
The 5G standard is a compromise.
For errors that can only occur during function entry, such as omitted required arguments, 5G requires they display as messages.
Errors in inputs that can occur later will display as error codes by default. We highly recommend providing diagnostic messages for those errors that users can select by setting an optional Diagnostics argument to TRUE so when mistakes are made we can assist component consumers in correcting entry errors.
Errors in the result, when no errors are in the inputs, display as error codes.
5G Error messages with optional Diagnostic argument
Template Source
At right is a template source.
0-This is our source description block which we can ignore for this discussion.
1-This is where we declare our arguments.
NOTE! All arguments are entered as optional so we can prevent Excel from blocking inline help and required argument messages.
NOTE! This template includes suggested the Diagnostics argument.
2-This is our inline help discussed in a prior class.
3-This is where named constants (if any) should go. We can ignore them for this class.
4-Here is where we check arguments. It is divided up into 4 sections. The order of these section is not important.
A-Set defaults for omitted optional arguments.
B-Create an error flag array for omitted required arguments (OmittedArgs). Set the inline help request (Help?) to TRUE if all required arguments are missing.
C-Create an error flag array (ErrorsInArgs) for arguments containing Excel error condes.
D-Create an error flag array (DVErrors) for arguments that do not pass data validation rules.
5-If section 4 found any errors, set AnyErrors? to TRUE.
6-Builds an array of error messages. OmittedArgs always generate messages. ErrorsInArgs and DVErrors only generate messages if the Diagnostics argument is set to TRUE.
7-Our function logic goes here.
8-We determine what to return here: Inline help, error messages, a #VALUE! error code, or results.
9-Return what was decided in section 8.
5G template source
Setting Defaults for Optional Arguments
The most generic approach is to test if the optional argument was omitted (ISOMITTED) and so, provide the default. If not, return the argument to itself.
If the argument must be of a certain type, we can use ISLOGICAL(), ISNUMBER(), ISNONTEXT(), and ISTEXT() instead. If the argument is of the correct type, return the argument to itself. If not, we can choose to return an error code (#VALUE!, #REF!, #NA) or the default.
NOTE! My preference is to act like an executive's assistant who corrects their bosses typos. You are free to disagree.
Omitted Required Arguments
The is an easy test. Just use ISOMITTED() and place all results in a vertical array.
NOTE! If there is just one required argument we do not need the VSTACK() function. I place it in the template because multiple required arguments seem to be the most often occurrence and having the VSTACK() in the template reminds me to stack the flags.
Errors in Arguments
The is an easy test. Just use ISERROR() and place all results in a vertical array.
NOTE! If the argument cannot be an array, OR() is not required. I typically keep the OR() rather than expending effort considering, "is it possible this will ever be an array?" It doesn't hurt to have it.
NOTE! If there is just one argument we do not need the VSTACK() function. I place it in the template because multiple required arguments seem to be the most often occurrence and having the VSTACK() in the template reminds me to stack the flags.
Data Validation
This is where we apply all the other rules that our arguments must meet. It is a bit tricky because, in general, we do not want to bombard component consumers with messages when correcting the first problem may eliminate all other problems. So if the argument has not been omitted, and does not contain any errors, then we should check it the argument meets requirements like:
Must be greater than 0
Must not be 0
Must be a future date
Must add to 100%
etc.
Once again, we use VSTACK() to build an array of data validation error flags.
Lab
Add argument validation to this 5G component.
Summary
5G is about creating components for other people to use. This requires handling mistakes and providing help when asked for. Preventing end user mistakes almost always consumes 80% of a routine's development effort but is critical to preventing, as best we can, customer frustration.
Assessment
Coming soon.