HandsOn Using Optional Arguments

1. In the Visual Basic Editor window, choose Insert | Module to add a new module.

2. Type the following Avg function procedure in the Code window:

Part I

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

totalNums = totalNums - 1 End If

End Function

3. Call this function from the Immediate window:

As soon as you press Enter, Visual Basic displays the result: 2.5.

This time the result is: 3.3333333333333.

^^ Note: If you are working with Acc2003_Chap04.mdb file from the book's downloadable files and you entered the Avg function in a brand new module, the ambiguous name message will pop up because this function is already entered in another module. If you run into this problem, simply replace all the occurrences of Avg in your module with another name.

As you've seen, the Avg function allows you to calculate the average of two or three numbers. You decide what 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. Arguments numl and num2 are required. 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 types of the numl, num2, and num3 arguments are not declared, Visual Basic treats all of these arguments as Variants.

Inside the function procedure, the totalNums variable is declared as an 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 the value of zero (0) in its place, and deducts the value of 1 from the value stored in the totalNums variable. Hence, if the optional argument is missing, the 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.

Introduction to Access 2003 VBA Programming

0 0