Extending complex UDFs with simple VBA

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.


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

3 Responses to Extending complex UDFs with simple VBA

  1. Farhad says:

    Hi Thank you very much for your wonderful blog. I dn’t know much about VBA but I am really interested in using your excel sheets. Particularly, I am into working with Units4Excel and the EvalU function. But like another user had mentioned above, it is good the first time you open it and will render errors as soon as you re-evaluate it or change a unit. I saw that you had updated the code to fix this, but in my case, it did not fix the problem. Do you have any idea what might be the problem?


  2. Farhad says:

    I think I just realized the problem. I was using a french language windows wher the decimal symbol was preset to “,” instead of point ” .” . I changed it and the worksheet started to work correctly. Thank you so much!


Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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