UDF to convert a value to feet & inches

JenniferMurphy

Well-known Member
I was able to find several solutions for converting a value to feet and inches where the inches are expressed as an integer plus a fraction. I couldn't find one that expressed the inches as a floating point number (n.nnn..) with the ability to specify the number of decimal points. So I wrote one myself.

The trick is to convert everything to inches, because that's where the rounding is done. Then the feet can be split off and the remaining inches formatted.

Here's the code and them some examples. I'd appreciate any critiques.

Code:
``````'**************************************************************************************************
'                    Convert a Length (number) to Feet & Inches (string)
'
'  Syntax: CvtLen2FtIn(pInVal, [pInUnits|"ft"], [pDPOut|2])
'
'     pInVal    = The number to be converted (real)
'     pInUnits  = The units for pInVal ("ft" (default) or "in")
'     pDPOut    = Number of decimal places in the output (default: 2, range: 0-5))
'     Result    = The converted value as a text field (nn' n.nnn..")
'**************************************************************************************************
Public Function CvtLen2FtIn(pInVal As Double, _
Optional pInUnits As String = "ft", _
Optional pDPOut As Integer = 2) As String

Const MyName As String = "CvtLen2FtIn"  'Function name for error messages
Dim TotInch As Double                   'Total length in inches
Dim FeetInt As Double                   'Whole number of feet
Dim Inches As Double                    'Remaining inches, rounded appropriately
Dim Fmt0s As String                     'Format string ("[.]000...")
Dim msg As String                       'Text of error messages

'Convert everything to inches. That's where the rounding will be done.
Select Case UCase(pInUnits)
Case "IN"
TotInch = pInVal       'No conversion, it's already in inches
Case "FT"
TotInch = pInVal * 12  'Convert from feet to inches
Case Else
CvtLen2FtIn = CVErr(xlErrValue)
Exit Function
End Select

If pDPOut < 0 Or pDPOut > 7 Then  'Check range of pDPOut (0-5)
CvtLen2FtIn = CVErr(xlErrValue)
Exit Function
End If

Inches = Round(TotInch, pDPOut)    'Round the inches to the specified number of decimal places
FeetInt = Int(Inches / 12)      'Whole number of feet
Inches = Inches - FeetInt * 12  'Remaining inches rounded appropriately
If pDPOut > 0 Then
Fmt0s = "0." & String(pDPOut, "0")
Else
Fmt0s = "0"
End If

CvtLen2FtIn = FeetInt & "' " & Format(Inches, Fmt0s) & """"

End Function``````
In this table, columns C & D contain the input data. I entered the feet and inches separately so I could compare the results. Column F contains the values in feet (feet + (inches/12). Column G the result of passing that value to the UDF. Columns H & I do the same thing, but with inches. Column J compares the two results to make sure they are identical.

 R/C C D E F G H I J 3 Input Total Converted Total Converted G = I? 4 Feet Inches DP Feet Result Inches Result TRUE 5 0 0 0 0 0' 0" 0 0' 0" TRUE 6 0 0 2 0 0' 0.00" 0 0' 0.00" TRUE 7 0 0 4 0 0' 0.0000" 0 0' 0.0000" TRUE 8 2 6 0 2.5 2' 6" 30 2' 6" TRUE 9 2 6 3 2.5 2' 6.000" 30 2' 6.000" TRUE 10 1 11.55 0 1.9625 2' 0" 23.55 2' 0" TRUE 11 1 11.555 1 1.962917 1' 11.6" 23.555 1' 11.6" TRUE 12 1 11.555 3 1.962917 1' 11.555" 23.555 1' 11.555" TRUE 13 1 11.555 4 1.962917 1' 11.5550" 23.555 1' 11.5550" TRUE 14 4 11.99999 0 4.999999 5' 0" 59.99999 5' 0" TRUE 15 4 11.99999 2 4.999999 5' 0.00" 59.99999 5' 0.00" TRUE 16 4 11.99999 4 4.999999 5' 0.0000" 59.99999 5' 0.0000" TRUE 17 4 11.99999 5 4.999999 4' 11.99999" 59.99999 4' 11.99999" TRUE 18 9 0.0001 0 9.000008 9' 0" 108.0001 9' 0" TRUE 19 9 0.0001 3 9.000008 9' 0.000" 108.0001 9' 0.000" TRUE 20 9 0.0001 4 9.000008 9' 0.0001" 108.0001 9' 0.0001" TRUE

<tbody>
</tbody>

Thanks

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

1,102,772
Messages
5,488,764
Members
407,657
Latest member
Sassy5

This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...