No, this is not advice on how to win a contentious social interaction - enough of that already! Arguments are the input values to a function. They can be required or optional, numeric, text, Boolean, range or array data type. Today we'll look at two strategies for designing function arguments: combined binary options, and argument default value and range definition.
First, to review guidelines for arguments:
All optional or as few required as possible (and not too many!)
Defaulting, ranging and error handling of arguments in the function code
Include alternative output selection (avoid function proliferation)
Clear user-friendly names
Clear function description describing each argument. Function description precedes the function and is denoted by /**description*/
Function description explains arguments, values and defaults
Cell entry prior to parenthesis displays the function description
Cell entry after parenthesis displays argument list
Combined Binary Options
We often use binary switches to turn things on and off, like a light. A simple on/off switch may be called a toggle switch, or in logical terms, a Boolean variable. In spreadsheet design, a checkbox control is a visually intuitive way to present a toggle. In function design, a Boolean argument is one that is evaluated as true or false, no matter what value it has. Zero or blank is evaluated as false, any non-zero number is evaluated as true. This is useful to define optional features that can be turned on by supplying a non-zero value, e.g. 1.
What if you have several Boolean options? Of course, you can define a separate argument for each one, which can become cumbersome. Especially when the options are related, it's helpful to combine several Booleans into one argument.
Consider how weekends are defined in WORKDAY.INTL and NETWORKDAYS.INTL. When you enter either function, you get a picklist of 14 values for weekend - 1 to 7 and 11 to 17 defining the most common weekend patterns (oddly enough, single digits for 2-day weekends, double digits for 1-day), but you also have the option of entering a seven-character text string of 0's and 1's, as described in the function help:
Weekend string values are seven characters long and each character in the string represents a day of the week, starting with Monday. 1 represents a non-workday and 0 represents a workday. Only the characters 1 and 0 are permitted in the string. 1111111 is an invalid string.
For example, 0000011 would result in a weekend that is Saturday and Sunday.
This means you can define 127 (2⁷ -1) unique workweek patterns with one argument instead of seven.
Where I encountered this challenge was in the calendar function design. It was already complex and had several arguments, but I needed to toggle date flags as one or more of four date types, weekend, holiday, annual and today, as well as to toggle display of the names. Five toggles would mean five more arguments, or some way to combine them into one.
I had already learned to dissect the weekend string noted above to pick and evaluate each day using MID(string,weekday,1). I could reapply that if I supplied a binary string in a similar way. I also wanted the option to use a decimal number representing each binary string value. This is not a user function, so I store it in module "i" for internal functions.
There are three required arguments
parameter: the combined value, either decimal number or binary string
position: which of the combined switches is being called
of: the number of switches
There are three steps:
line 325: determine if the input parameter is binary or decimal
line 326: if decimal, convert to binary and if error, return all zeroes
line 327: pick out the required character from the binary string and evaluate
Usage
As noted, this function is for internal use, never in a worksheet. It is called in other internal functions to extract each toggle from the parameter, for example:
i.binopt(parameter, 2, 5)
extracts the second of five concatenated toggles from "parameter".
An obvious point of concern is to identify and document each toggle. One way is to make an array like the one at right. A simpler way is to pick a letter to represent each toggle, concatenate them and name the parameter mnemonically. For the calendar I use "nwhat" - names, weekend, holiday, annuals, today. The second toggle is clearly the weekend flag:
i.binopt(nwhat, 2, 5)
The five toggles have 2⁵ = 32 combined options. In reality I almost always have all the flags on or off. But I know that 15 = "01111" = all flags on, names off; to turn off annuals, 13 = "01101"; to add names, 29 = "11101".
Combined arguments don't have to be Boolean to be efficiently parsed and passed within the function. Last week I gave the example of combining single-digit column numbers to format an output array. However, if you use a single argument to combine multiple attributes, each with multiple attribute values, you may reach a point where the user or even you can't keep track of all the options. With combined Booleans all you need to remember is the switch order.
If I had my wish list, Microsoft would enhance LAMBDA to allow picklist arguments. For now, it's easy enough to remember the few values I use, or just use a mnemonic with a binary string. Like a lot of what I do, this is purposeful overkill in search of the balance between simplicity and utility. As Microsoft did with the weekend string, I've built in the flexibility to avoid future rework.
Default and Ranged Argument Values
Optional arguments always have a default value, implicit or explicit, which is in effect when one is not supplied by the user. If an omitted entry (0 or FALSE) is the desired default, it is implicit, and no definition is needed. Only if a non-zero value is given is the relevant feature turned on. This is normally the case for Boolean arguments, and for non-Boolean arguments with default zero. But non-Booleans can have a non-zero default, in which case it must be explicit, for example, IF(value,value,default).
For efficiency I often build multiple output formats into one function, designated by number such as 1 to 4, then use CHOOSE to return the desired output. For error prevention I force the input into the valid range by combining MIN and MAX, which also defines the default.
x | MAX(x,1) | MIN(x,4) | MIN(MAX(x,1),4) |
< 1 e.g. blank | 1 | x | 1 = default |
1 - 4 | x | x | x |
> 4 | x | 4 | 4 |
The easy way to remember this ranging method is MIN ( MAX ( x, lo ), hi )
Consider the function m.loan below:
There are three required and three optional arguments. Each optional argument requires a conditional statement - "if supplied or not":
[output], line 267: the input value is ranged between 1 and 4, default 1
[compound], line 262: Boolean switch, default OFF
[begin], line 263: Boolean switch, ranged to 1 or 0
Comentários