Using ParamArray()

[ADezii]

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.