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.