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.
Assessment
Did you get the important points of this section? Take this quiz:
1) Why does 5g need the Advanced Formula Environment when creating 5G functions?
a) To add all three comment types to LAMBDA formulas.
b) To format LAMBDAs with color
c) LAMBDAs cannot be created without it
2) Which of the Advanced Formula Environments editors allow all three comment types?
a) Grid
b) Names
c) Modules
3) Why are comments important?
a) They help identify LAMBDA sections
b) They provide revision history and version information.
c) They help those who must review our LAMBDA understand what it does.
d) They help those who must maintain our LAMBDA
e) All of the above.
4) What kind of comment is this? \** comment here *\
a) Name Manager comment.
b) Single line comment
c) Multiline comment
d) This is not a valid comment
5) What kind of comment is this? \\ comment here
a) Name Manager comment.
b) Single line comment
c) Multiline comment
d) This is not a valid comment
6) What kind of comment is this? \* comment here *\
a) Name Manager comment.
b) Single line comment
c) Multiline comment
d) This is not a valid comment
7) In our template which section comes first, second, third, etc.
a) Inline help
b) Named Constants
c) Procedure
d) Documentation Block
e) Function Return
f) Argument Declaration
g) Function Declaration
h) Omitted Required Arguments
i) Optional Argument Defaults
8) Which section includes the revision log?
a) Inline help
b) Named Constants
c) Procedure
d) Documentation Block
e) Function Return
f) Argument Declaration
g) Function Declaration
h) Omitted Required Arguments
i) Optional Argument Defaults
9) Which section provides the LAMBDA's distinct functionality?
a) Inline help
b) Named Constants
c) Procedure
d) Documentation Block
e) Function Return
f) Argument Declaration
g) Function Declaration
h) Omitted Required Arguments
i) Optional Argument Defaults
Assessment Answers
Assessment Answers
1) a We can create LAMBDAs in Name Manager w/o AFE but we cannot add all comment types without it. Colors are nice but not critical.
2) c
3) e
4) a
5) b
6) c
7) d, g, f, a, h, b, i, c, e
8) d
9) c
Navigation
Page updated
Google Sites
Report abuse