Parameter Arrays

In Chapter 4 you learned that values can be passed between subroutines or functions as required or optional arguments. If the passed argument is not absolutely required for the procedure to execute, the argument's name is preceded by the keyword Optional.

Sometimes, however, you don't know in advance how many arguments you want to pass.

A classic example is addition. You may want to add together two numbers, or, you may use three, ten, or fifteen numbers. Using the keyword ParamArray, you can pass an array consisting of any number of elements to your subroutines and function procedures.

The following AddMultipleArgs function will add up as many numbers as you require. This function begins with the declaration of an array, myNumbers. Notice the use of the ParamArray keyword. The array must be declared as an array of type Variant, and it must be the last argument in the procedure definition.

1. Insert a new module into the current project and rename it ParameterArrays.

2. Enter the following AddMultipleArgs function procedure:

Function AddMultipleArgs(ParamArray myNumbers() As Variant) Dim mySum As Single Dim myValue As Variant

For each myValue in myNumbers mySum=mySum+myValue

Next

AddMultipleArgs = mySum End Function

3. To try out the above function, activate the Immediate window and type the following instruction:

When you press Enter, Visual Basic returns the total of all the numbers in parentheses: 93.24. You can supply an unlimited number of arguments. To add more values, enter additional values in parentheses and press Enter. Notice that each function argument must be separated by a comma.

0 0

Post a comment