Creating Custom Functions with Optional Arguments

Some of the built-in Excel 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 use 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 a² + b² = c², 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 the user of the function can supply any two arguments and the function will solve for the third argument. The following code does the trick:

 Function Triangle(0ptional 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 next 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. 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 (3² + 4²). You could also write the formula =Triangle(3,4,); however, 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 arguments when it has already discovered that none are missing.

You can use a similar If... End If construction to look 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 the built-in error constants. If you call the function and offer a text argument, the function returns #VAEUE! 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 try to do so.