Parameter Arrays

Earlier in this book 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. One time you may want to add two numbers together, another time you may want to add three, ten, or fifteen numbers.

Part I

Using the keyword ParamArray you can pass to your subroutines and function procedures an array consisting of any number of elements. The following hands-on uses the AddMultipleArgs function to add 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.

(( Hands-On 7-12: Working with Parameter Arrays

1. In a new module, 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

2. 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 the parentheses: 93.24. You can supply an unlimited number of arguments. To add more values, enter additional values in the parentheses and press Enter. Notice that each function argument must be separated by a comma.

0 0

Post a comment