Using the VBA ParamArray keyword

The recently posted SolvePoly function makes use of the VBA ParamArray keyword, which allows an arbitrarily long list of arguments to be passed to another function.  I have not made great use of this in the past, but since the structure is similar to a Python list of lists it forms a convenient way of transferring data to and from Python routines, as well as being useful in a pure VBA context.

The full code of the PolySolve function is shown below:

Function SolvePoly(ParamArray CoeffA() As Variant)
Dim i As Long, PArray As Variant, Num_Coeff As Long

    Num_Coeff = UBound(CoeffA) + 1
    ReDim PArray(1 To Num_Coeff, 1 To 1)
    For i = 0 To Num_Coeff - 1
        PArray(i + 1, 1) = CoeffA(i).Value2
    Next i

    Select Case Num_Coeff
    Case Is < 3:
        SolvePoly = "Num_Coeff must be >= 3"
    Case Is < 4:
        SolvePoly = Quadratic(PArray)
    Case 4:
        SolvePoly = CubicC(PArray)
    Case 5:
        SolvePoly = Quartic(PArray)
    Case Else:
        SolvePoly = RPolyJT(PArray)
    End Select
    SolvePoly = WorksheetFunction.Transpose(SolvePoly)
End Function

The rules for using ParamArray are:

  • Any non-optional arguments must be declared first.
  • The argument passed by ParamArray must be declared as a variant array, including the “()”.
  • ParamArray cannot be used in conjunction with ByVal, ByRef or Optional.
  • Each argument in the ParamArray array may be any data type, including variant arrays, ranges or objects.
  • The argument passed by ParamArray is a 1D base 0 array
  • Each argument is optional, but if optional arguments are expected they must be checked using the IsMissing function.  In the case of the SolvePoly function an empty argument in the list (e.g. =solvepoly($B$12,$B$13,,C12)) will cause an error at the line:  PArray(i + 1, 1) = CoeffA(i).Value2
  • When calling the function, either from the worksheet or from another VBA function, each argument is listed separately.  They are combined into a variant array automatically.

In the case of the SolvePoly function the CoeffA argument was required to pass an arbitrary number of double values, which were then converted into a 2D array, as expected by the subsequently called functions.  A Select Case statement was then used to call one of 4 functions, depending on the length of the PArray array.  Note that the final RPolyJT function will accept an array of any length, so PolySolve will handle any number of input arguments greater than 2.

This entry was posted in Excel, Maths, Newton, UDFs, VBA. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s