5G: Validating Function Arguments

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. 

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.

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.