5g: Standards
Description
This section explains what makes a function a 5g function.
Learning Objectives
At the end of this section, learners will know:
Why 5g is needed.
What the 5g guiding principles are.
The 5g standards derived from the guiding principles.
Prerequisites
This section has no prerequisites.
Discussion
How is 5g different from LAMBDA?
(To learn more about 5g in general and why it is important click 5g Explained.)
5g is a standard for converting Excel formulas into simple functions that anyone of any skill level can use in any workbook. Creating functions for others is different than creating functions for our own use. When we create functions for ourselves we are:
Free to figure things out as we go and make massive changes whenever.
Free to use cryptic naming because we know what the function is for and what the parameters are.
Free to neglect structure because we know where we put things.
Free to neglect testing because when it fails, we can fix it.
These freedoms can create disaster for our clients. When we create for others we must make sure:
Things are figured out before hand because we won't have access to the function once handed to others.
Naming is meaningful because users won't be able to read our minds
Our functions are structured so others can more easily maintain the functions we gave them when circumstances don't allow us to maintain them.
Thoroughly test our functions to protect our clients and our reputation.
5g Guiding Principles
The guiding principles for creating 5g functions are end user focused: 5g functions must:
Be simple to use.
Have the look and feel of Excel's functions that everyone is familiar with.
Have inline help like Excel's functions, to guide users on how to use the function.
Have meaningful names to avoid confusing users as to the function's purpose.
Be easily imported into workbooks.
Display version information so end users can know if they have the most up-to-date version in their models.
Maintain backward compatibility when updated.
Be error free as confirmed by testing before offering to others.
Be 100% self contained so the function works in any workbook.
Be structured and documented so third party reviewers can easily understand the function's inner working, and so sufficiently skilled users can maintain them when the function's creator can't.
5g Standard
From the guiding principles we created the following standards for 5g compliant functions:
Functions are created from 100% native Excel functions and operators.
Functions have meaningful and concise names.
Function arguments have meaningful and concise names.
All arguments are are enclosed in square brackets so they look to Excel as optional.
Inline help appears when any required (as determined by the function's formula) argument is missing. Inline help must contain:
Function name and syntax
Brief description
(optional) Webpage / URL
Version date
Argument names with (Required) or (Optional) and a brief description
Functions are 100% self contained so they can work in any workbook running under any version of Excel that supports LAMBDA.
All information needed by the function is inside the function except for information brought in through the function's arguments
No cell references, name manager references, or table references
No VBA or other scripting
A documentation block appears at the top of the function's source. It must contain:
The function's name
A brief description formatted for Name Manager's comment box
Revision log
LET() steps have meaningful and concise names.
Where practical, LET() steps are short and concise.
Functions must be stored in a GitHub Gist for easy importing by Microsoft's Advanced Formula Environment.
Functions must be thoroughly tested before releasing to the public.
New function versions must be backward compatible with previous versions.
Summary
Creating functions for others requires more thought and effort. The standards guide us through the process of making our functions highly usable by others and thus, successful.