Embedded Files
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 function value is realized in frequent reuse. Frequent reuse can be realized when functions:
Can be applied to a wide variety of similar scenarios.
Are simple to use, requiring no special skills by the user.
Have unique, clear and concise names helping users understand a function's purpose.
have the familiar look and feel of Excel's functions.
Are 100% self contained so they can be used in any workbook.
Are easily imported into workbooks.
5g functions must be Error free. To support this:
Version information is required to help users know when updates are available where defects have been removed or enhancements added.
Being easily to update when versions are released encourages users to implement defect fixes or enhancements sooner.
Updates must maintain Backward compatibility to prevent errors that would be caused by incompatibility in existing implementations.
Thorough testing is the best way to detect errors.
Functions must be easy to maintain by others when the function creator cannot.
Functions that are well structured and documented are easier to maintain.
5g Standard
From the guiding principles we created the following standards for 5g compliant functions:
5g functions must be designed for reuse, preferably, for a wide variety of scenarios.
To maintain the look and feel of Excel's functions, 5g functions must:
Have unique, clear and concise names to avoid confusing users as to the function's purpose and to avoid confusion with Excel's native functions.
Display a tooltip (currently only supported in desktop Excel) when users enter them in formulas.
Display clear and concise argument names when users enter arguments.
Have inline help to guide users on how to use the function.
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
To support inline help display, all arguments must be enclosed in square brackets so they look to Excel as optional and support the inline help function.
To support operation in any workbook, functions are created from 100% native Excel functions and operators.
To support operation in any workbook, functions must be 100% self contained
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
EXCEPTION! 5g Functions may reference other 5g functions that are in the same module (aka library).
To support version control, a documentation block must appear 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() step formulas are short and concise.
To support debugging, the last LET step should be the LET step name to return.
Functions are stored in a GitHub Gist for easy importing by Microsoft's Advanced Formula Environment.
New function versions are backward compatible with previous versions.
Functions are thoroughly tested before releasing to the public.
Summary
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.
Page updated
Google Sites
Report abuse