## Using Optional Arguments

At times you may want to supply an additional value to a function. Let's say you have a function that calculates the price of a meal per person. Sometimes, however, you'd like the function to perform the same calculation for a group of two or more people. To indicate that a procedure argument is not required, precede the name of the argument with the Optional keyword. Arguments that are optional come at the end of the argument list, following the names of all the required arguments. Optional arguments must always be the Variant data type. This means that you can't specify the optional argument's type by using the As keyword.

In the preceding section, you created a function to calculate the average of three numbers. Suppose that sometimes you'd like to use this function to calculate the average of two numbers. You could define the third argument of the MyAverage function as optional. To preserve the original MyAverage function, let's create the Avg function to calculate the average for two or three numbers.

2. Activate the Sample5 module and enter the function procedure shown below:

Function Avg(num1, num2, Optional num3) Dim totalNums As Integer totalNums = 3 If IsMissing(num3)Then num3 = 0

totalNums = totalNums -1 End If

Avg = (num1+num2+num3)/totalNums End Function

3. Now call this function from the Immediate window: ?Avg(2,3)

As soon as you press Enter, Visual Basic displays the result: 2.5. ?Avg(2,3,5)

This time the result is: 3.3333333333333.

As you've seen, the Avg function allows you to calculate the average of two or three numbers. You decide which values and how many values (two or three) you want to average. When you start typing the values for the function's arguments in the Immediate window, Visual Basic displays the name of the optional argument enclosed in square brackets.

Let's take a few minutes to analyze the Avg function. This function can take up to three arguments. The arguments numl and num2 are required. The argument num3 is optional. Notice that the name of the optional argument is preceded with the Optional keyword. The optional argument is listed at the end of the argument list. Because the type of numl, num2, and num3 arguments is not declared, Visual Basic treats all of these arguments as Variants.

Inside the function procedure, the totalNums variable is declared as Integer and then assigned a beginning value of 3. Because the function has to be capable of calculating an average of two or three numbers, the handy built-in function IsMissing checks for the number of supplied arguments. If the third (optional) argument is not supplied, the IsMissing function puts in its place the value of zero (0), and at the same time it deducts the value of one from the value stored in the totalNums variable. Hence, if the optional argument is missing, totalNums is 2. The next statement calculates the average based on the supplied data, and the result is assigned to the name of the function.

The IsMissing function allows you to determine whether or not the optional argument was supplied. This function returns the logical value True if the third argument is not supplied, and it returns False when the third argument is given. The IsMissing function is used here with a decision making statement If.. .Then (see Chapter 5 for a detailed description of this and other decision-making statements used in VBA). If the num3 argument is missing (IsMissing), then (Then) Visual Basic supplies a zero for the value of the third argument (num3 = 0) and reduces the value stored in the argument totalNums by one (totalNums = totalNums - 1).

How else can you run the Avg function? On your own, run this function from a worksheet. Make sure you run it with two and then with three arguments.

Tip 4-10: Testing a Function Procedure

To test whether a custom function does what it was designed to do, write a simple subroutine that will call the function and display its result. In addition, the subroutine should show the original values of arguments. This way, you'll be able to quickly determine when the values of arguments were altered. If the function procedure uses optional arguments, you'll also need to check those situations in which the optional arguments may be missing.

0 0