CREATING CUSTOM FUNCTIONS WITH OPTIONAL ARGUMENTS

Some of Excel’s built-in functions let you omit certain arguments. For example, if you omit the type and future value arguments from the PV function, Excel still computes the result because those arguments are optional. Your custom functions can also make use of optional arguments.

For example, suppose you want to create a custom function called RightTriangle that uses the Pythagorean theorem to compute the length of any side of a right triangle given the lengths of the other two sides. The equation that expresses the Pythagorean theorem is a2 + b2 = c2, in which a and b are the short sides and c is the hypotenuse. Given any two sides, you can use this equation to solve for the third side.

In a general-purpose Triangle function, you want to accept three arguments (one for each side of the triangle) but make each argument optional so that the user of the function can omit the argument that the function should solve for. The following code does the trick:

    Function Triangle(Optional side1, Optional side2, _
        Optional hypotenuse)-
        If Not(IsMissing(side1)) And Not (IsMissing(side2)) Then
            Triangle = Sqr(side1 ^ 2 + side2 ^ 2)
        Else
            If Not(IsMissing(side1)) And Not(IsMissing(hypotenuse)) Then
                Triangle = Sqr(hypotenuse ^ 2 - side1 ^ 2)
            Else
                If Not(IsMissing(side2)) And Not(IsMissing(hypotenuse)) Then
                    Triangle = Sqr(hypotenuse ^ 2 - side2 ^ 2)
                Else
                    Triangle = "Please supply two arguments."
                End If
            End If
        End If
    End Function
The first statement names the custom function and the optional arguments side1, side2, and hypotenuse. The following block of code contains a series of If statements that use the VBA IsMissing function to test whether each possible pair of arguments has been supplied and to calculate and return the length of the unknown side:
    If Not(IsMissing(side1)) And Not (IsMissing(side2)) Then
        Triangle = Sqr(side1 ^ 2 + side2 ^ 2)
This code tests for the presence of side1 and side2. The IsMissing function returns True if the argument has not been supplied. If side1 is not missing and side2 is not missing, Excel computes the square root of the sum of the squares of the two short sides and returns the length of the hypotenuse to the worksheet.

If fewer than two arguments are supplied, the following statement returns a text string to the worksheet:
    Triangle = "Please supply two arguments."
Now let’s see what happens when we use this custom function in a worksheet formula. The formula =Triangle(3,4) returns 5. The hypotenuse argument is omitted, so the function returns the square root of (32 + 42). You could also write the formula =Triangle(3,4,), but the second comma is not necessary. The formula =Triangle(,4,5) returns 3 because the side1 argument is omitted. The formula =Triangle (4,,5) also returns 3.

The function as written has at least two flaws. First, if the user supplies all three arguments, the function behaves as though the third argument were omitted. You might prefer to have it return an error message. Second, the function accepts negative and zero arguments even though triangles cannot have sides of negative or zero length. You can eliminate the first of these defects by adding the following If … End If block immediately after the Function statement:
    If Not(IsMissing(side1)) And Not(IsMissing(side2)) And _
        Not(IsMissing(hypotenuse)) Then
        Triangle = "Please supply only two arguments."
        Exit Function
    End If
Note that this block includes an Exit Function statement. This saves the function the trouble of searching for missing argument when it has already discovered that none are missing. You can use a similar If … End If construction to check for arguments less than or equal to zero, returning an appropriate error message and exiting the function if any are found. Note that other kinds of inappropriate arguments (text, for example) will cause the function to return one of Excel’s built-in error constants. If you call the function and offer a text argument, the function returns #VALUE! because it attempts to perform arithmetic operations on a nonarithmetic value.

How much error trapping you add to your custom functions depends, of course, on how much work you want to do and how you plan to use the function. If you’re writing a function for your personal use, you might not need to deal with every conceivable aberrant use. If you write the function for others, you’ll probably want to eliminate all possibility of error—or at least to try to do so.