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.