Generally, the number of Arguments in a Procedure Call must be the same as in the Procedure Specification. If a Procedure requires 3 Arguments in its Specification, when calling the Procedure you must pass exactly 3 Arguments which should be of the specific Data Type and in the correct order as defined by the Procedure itself. A simple case will illustrate this point:
-
Public Function fCalculateInterest(curPrincipal As Currency, sngRate As Single, lngTermInMonths As Long) As Currency
-
fCalculateInterest = curPrincipal * sngRate * lngTermInMonths
-
End Function
-
To the fCalculateInterest() Function, you should pass a CURRENCY, SINGLE, and LONG value, and the Function will then return a CURRENCY Data Type.
A typical call to this Function may look something like:
-
Dim curInterest As Currency
-
curInterest = fCalculateInterest(75000, .0675, 240)
-
By using the ParamArray Keyword, you can specify that a Procedure will accept an arbitrary number of Arguments. Each Argument to a ParamArray Parameter can be of a different Data Type. The Parameter itself must be declared as an Array of Type Variant. When the call is made, each Argument supplied in the call becomes a corresponding element of the Variant Array. Again, a simple code segment will illustrate this point. Several calls will be made to the fAverageNumbers() Function, each with a varying number of Arguments, some even with invalid Arguments. The Function definition along with several calls and associated Outputs will be demonstrated:
-
Public Function fAverageNumbers(ParamArray varNumbers())
-
Dim varX, varY, intNumsToAverage As Integer
-
-
intNumsToAverage = 0
-
-
For Each varX In varNumbers
-
If IsNumeric(varX) And Not IsNull(varNumbers) Then
-
varY = varY + varX
-
intNumsToAverage = intNumsToAverage + 1
-
fAverageNumbers = varY / intNumsToAverage
-
Else
-
End If
-
Next
-
End Function
-
Sample Calls and Outputs:
-
Debug.Print fAverageNumbers(20, 80, 50) 'produces 50
-
Debug.Print fAverageNumbers(2, 4, 8, 16, 32, 64, 128, 256, 512) 'produces 113.555555555556
-
Debug.Print fAverageNumbers(98.765, 34.988, 1004.56) 'produces 379.437666666667
-
Debug.Print fAverageNumbers(Null, "Number", 500, 100) 'produces 300
-
Debug.Print fAverageNumbers(16) 'produces 16
-
NOTE: In my humble opinion, the ParamArray() Statement is an extremely valuable and versatile programming tool which can be used whenever the number of Arguments to be passed to a Procedure, as well as their Data Types are not known.
