Embedded Files
Description
5g functions are for others to use. This requires extra work. To minimize the extra work we offer a template that helps ensure we complete all 5g requirements and, in some places, provides the code for those requirements.
Learning Objectives
At the end of this course, learners will know:
What the various 5g function source sections are
And how they are organized
Prerequisites
Microsoft's free add-in: Advanced Formula Environment (AFE)
Discussion
Farther down this page is a template for creating 5g functions. The various sections are numbered. We will review what each section does at a high level and why it is in the position that it is in. In later classes we will explain each section in detail.
Before we get into what each section does, note the colors. These are the colors that Microsoft's free add-in, Advanced Formula Environment (AFE), adds to source.
Purple is for the LAMBDA's name.
Green is for comments. NOTE! AFE's module editor is the only editor that allows all comments. There are three types of comments.
// designates a single line comment. Everything to the right of the double slash is treated as a comment until the first new line (paragraph).
/* designates a multi-line comment. Everything between a slash asterisk to an asterisk slash */ are treated as a comment.
/** designates a multi-line comment that will be added to Name Manager's comment box. Everything between a slash double asterisk to an asterisk slash */ is treated as a comment. There can only be one of this type of comment per named LAMBDA.
Red is for literal text and strings.
Blue is for identifies such as named formulas within the LAMBDA.
Dark Blue is for reserved symbols (operators, commas, brackets. etc.) and keywords, like Excel's function names.
Comments are only available in AFE's modules tab (See below).
The Template
Below is BXL's 5g template. It can be downloaded using AFE from this URL:
https://gist.github.com/CHatmaker/3e6cba6414fadfabfa3725255da2ba74
NOTE! The circled numbers are NOT part of the code. There are added to mark sections for discussion.
Documentation Block - This section is where we can add a comment that appears in the Name Manager. We also add our revision log. The revision log also serves as a version identifier.
Change <enter name here> to your function's name.
Change <changed> to the date this was edited. Recommend yyyy-mmm-dd format.
Change <your name> to your name
Change <what changed> to a brief description of what was done. If this is when the function was created, we suggest either a copyright notice or "original development".
Function Declaration - Here is where we declare our function's name. We recommend using the lambda symbol (λ) at the end of the function name to draw attention to the fact this is a user defined function. Excel's IntelliSense will make it so no one has to enter that symbol when using our function.
Argument Declarations - All function arguments must be declared here. Argument names must be meaningful to strangers.
All arguments must be enclosed in square brackets to enable inline help to function. To Excel, this makes function arguments optional which allows users to enter the function name without any arguments. When the required arguments are missing, our function can use that to trigger the inline help.
Inline Help - All Excel functions have inline help. At this time, Microsoft has no facility for adding hypertext pop-up inline help so we came up with a way to display inline help in Excel's cells using the TEXTSPLIT() function. To control TEXTSPLIT's behavior when text contains commas, we replace TEXTSPLIT's default delimiters with → for columns, and ¶ for rows.
Omitted Required Arguments - We declared all parameters as optional so users could omit what the function requires which triggers the inline help display. We ONLY check what the function requires, and NOT what we deem optional for the function.
Named Constants - If we find ourselves adding hard coded numbers to our formulas, like 12 for 12 months, we urge creators to name those values in this section and use the name, rather than the value, in our formulas. This improves function readability.
Optional Argument Defaults - Set omitted optional arguments to their default values here.
Procedure - This is where we put the formulas that provide our function's functionality.
The last line in the procedure should be named Result.
Function Return - Here is where our function determines if it should return the result of our precedure's formulas, or the inline help.
Summary
Summary
Using this template reminds us what we should include and where things go. It also helps to ensure users know how to use our function and which version they are using. In addition, the structure makes it easier for us, or anyone else, to maintain our code.
Navigation
Page updated
Google Sites
Report abuse