One of the advantages of writing spreadsheet applications in the form of User Defined Functions (UDFs) is that the UDF may be used on the spreadsheet in the same way as a built-in function, allowing applications to be modified and extended with no additional coding.  Nonetheless, there are times when this approach becomes time consuming or limiting, and it is worth writing some additional VBA code to achieve a better solution.

The example presented in this post was prepared in response to a question at the Eng-Tips forum., looking for a way to sum a column of length dimensions, entered in text format in feet and fractional inches.

One way is to use a UDF to convert the text to decimal values in the adjacent column, sum this column using the Excel Sum function, then convert this vale back to feet and inches with as second UDF.

A much more convenient way can be generated with a few lines of VBA:

Function SumFtinf(FtinRange, Optional Denom As Long = 0)
Dim FtInVal As Range, DecSum As Double

For Each FtInVal In FtinRange
DecSum = DecSum + FtInf2m(FtInVal.Value2)
Next FtInVal

SumFtinf = M2Ftinf(DecSum, Denom)
End Function

This function uses two existing UDFs (from the Units4Excel spreadsheet).  FtInf2m converts a length text string, in feet and fractional or decimal inches, to a value in metres.  M2Ftinf converts the metric value back to feet and inches, with a specified fractional denominator, or decimal inches if Denom is not specified.  The work of the function is done with a simple For Each loop which:

• Works on each cell in the range defined by FtinRange, giving the name following “For Each” to each cell in turn.
• The value of each cell is extracted with the .Value2 property.  There is also a .Value property which will work, but .Value2 is quicker.  Note that the value in this case is a text string, not a number.
• The text string is converted to a metric value with the Ftinf2m function, and added to the running total in DecSum.
• After the For Each loop has looped through all the cells the metric total length is converted back to feet and inches with the M2Ftinf function, and this is the value returned by the SumFtinf function.

The name given to the active cell in the For Each loop may be any valid variable name.  The name “cell” is often used, which is descriptive, but may be confused with the “cells” object, which is an Excel object and not a valid variable name.

An example of the function in use is shown in the screenshot below:

The new function has been added to the Units4Excel spreadsheet, which also contains a variety of unit conversion functions, and functions for unit aware evaluation of mathematical functions entered as text.

Download Units4Excel , including full open source code.

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.