Although many functions need only one argument (such as the trigonometry functions and the AreaCircle() function from the previous example), there are also many functions that use two or more arguments to calculate the return value. To calculate the area of a trapezoid, three values have to be passed as arguments to the AreaQuad() function.

In the first line of the function block, you can enter several arguments separated by commas and enclosed in a pair of parentheses. You can enter the arguments in any order with one exception, mentioned later, in the section titled Functions with Optional Arguments. To add the three arguments Length1, Length2, and Height to the AreaQuad() function, declare the function as follows:

Public Function AreaQuad(Length1, Length2, Height)

For this example, you don’t always need three arguments for your function. This refers to the second bullet of the first enumeration of the AreaQuad() sample.

Optional arguments are arguments that are not needed for all function calls. Because the calculation of the function in the worksheet doesn’t impact the program code, you have to prepare for the case in which no values are passed to certain arguments at runtime as you create the function in the Visual Basic Editor. These optional arguments are identified by the keyword Optional preceding each name in the function declaration. The declaration must declare all required arguments before any optional ones.

Following these rules, a declaration for a function with two required and two optional arguments would be as follows:

Public Function DoItYourself(Always1, Always2, Optional Sometimes1, Optional
Sometimes2)

As you can see, you have to specify the keyword Optional for each optional argument. For the AreaQuad() function, enter the following declaration using the type Variant for the optional arguments:

Public Function AreaQuad(Length1 As Double, Optional Length2 As Variant, Optional
Height As Variant) As Double

This function declaration tells Excel that a custom function with the name AreaQuad, with the three arguments Length1, Length2, and Height, exists. Excel also knows that the first argument is required and is of type Double, whereas the last two arguments are optional.

However, Excel doesn’t know yet how to handle optional arguments without values. Therefore, you have to write the function in such a way that all possible combinations of used and not-used arguments are considered in the code. When you are using functions with optional arguments, you need to be able to establish whether an argument is passing a value. For this purpose, you can use the VBA IsMissing() function to check a special attribute of an optional argument. (This is a function that can be used only in the VBA module, not in a worksheet.)

What is the special attribute for? It isn’t obvious whether the value passed by the argument of a function is actually passed or is missing. An argument will always have a value, and you cannot use the value of an argument to identify an argument as optional. This information has to be passed in addition to the value of the argument. However, you cannot accomplish this with “pure” data types; you must use only the Variant data type. Note, too, that optional arguments always require the data type Variant and cannot be declared as Double. Because Variant is the standard data type, you don’t need an explicit declaration.

But how do you use a single function, AreaQuad(), to calculate the areas of a square, a rectangle, a parallelogram, and a trapezoid?

The VBA IsMissing() function is designed to solve this problem. Depending on the existence of an argument, the return value of this function is either TRUE or FALSE. If this function returns FALSE, the optional argument has a value and can be used in the calculation; otherwise, no value is passed. With this information and by using some logical VBA tests to branch, the solution is possible.

To formulate a logical condition, you use the If-Then condition. For example, for the AreaQuad() function you could say, “If the Height argument is missing, then calculate the area of the square or rectangle; otherwise (else), calculate the area of the parallelogram or trapezoid.” With the VBA IsMissing() function, the logical condition is If IsMissing(Height) Then, followed by the statements for the first calculation.

Next comes the Else keyword, which initiates the second calculation. End If completes the If block. A complete If block looks like the following example:

If condition Then
   Statement1
Else
   Statement2
End If

The condition is a logical expression that returns the logical value TRUE or FALSE. This expression could be a comparison, such as x > 2 or Age <= 50, or a function returning a logical value, such as IsMissing(). Depending on the logical value returned by the function, either Statement1 or Statement2 is used for the calculation.

Spacing and indenting the lines makes the code more readable. The following section provides an overview of the If statements in VBA and the options you can use to add branches to custom functions.

If Age  >=  50 Then Feature = 1

If Age >=  50 Then
   Feature = 1
End If

If Age >=  50 Then
   Feature = 1
Else
   Feature = 2
End If

If Age >=  50 Then
   Feature = 1
   ID = "ID17"
Else
   Feature = 2
   ID = ""
End If

If Age >= 50 Then Feature = 1 : ID = "ID17" Else Feature  = 2 : ID = ""

The If statement allows you to evaluate the logical value returned by the IsMissing() function and to decide what action to take if an argument is missing.

But the example is a little more complicated. If you find out that the Length2 argument is missing, the situation is still not clear, because the area of the square as well as the area of the parallelogram can be calculated without the Length2 argument. The absence of the Height argument indicates that the area of a square is being calculated, because only one argument is needed; if the Height value is present, the area of a parallelogram is being calculated.

Even if the Length2 argument is specified, you still have two alternatives: You could calculate the area of a rectangle or a trapezoid. The Height argument indicates whether the area of the rectangle or the trapezoid is calculated.

Therefore, two If blocks have to be nested in a parent If block. The different possibilities are shown in Table 6-1.

The first If block checks whether the second argument, Length2, exists and branches into the appropriate statement blocks:

If IsMissing(Length2) Then
...         'these are the statements for the square and parallelogram
Else
...         'these are the statements for the rectangle and trapezoid
End If

Depending on the existence of the Height argument, the internal If block indicates whether the square or parallelogram (in the first case) or the rectangle or the trapezoid (in the second case) are calculated.

If IsMissing(Height) Then
   AreaQuad = Length1 ^ 2    'Square
Else
   AreaQuad = Length1 * Height      'Parallelogram
End If

Finally, the block checks for the existence of the Height parameter to evaluate the rectangle/trapezoid options.

If IsMissing(Height) Then
   AreaQuad = Length1 * Length2    'Rectangle
Else
   AreaQuad = (Length1 + Length2) /2 * Height      'Trapezoid
End If

The following summarizes the steps of the AreaQuad() function:

Here is the complete program code:

Public Function AreaQuad(Length1 As Double, Optional Length2, Optional Height) As Double
If IsMissing(Length2) Then
   If IsMissing(Height) Then
           AreaQuad = Length1 ^ 2    'Square
   Else
           AreaQuad = Length1 * Height       'Parallelogram
   End If
Else
   If IsMissing(Height) Then
           AreaQuad = Length1 * Length2       'Rectangle
   Else
           AreaQuad = (Length1 + Length2) /2 * Height       'Trapezoid
   End If
End If
End Function

If the Length1 argument is missing, the custom function returns the #VALUE! error. This alerts you to the fact that the required Length1 argument is missing.