Decimal to feet and inches conversion

[dkotula]

I have a form with two text boxes, one called txtDecimal, and another called
txtLength. I want to enter a decimal value into txtDecimal, and output a feet
and inches value into txtLength. Here is code used preveously in excel for
this.
Could someone provide me with VB code to enter into this form, and point me in the right direction as to where I need to put it in my form.

Thanks
Dave

"Created by MrExcel"
Public Function lentext(Feetin As Double)
' This function will change a decimal number of feet to the text string
' representation of feet, inches, and fractional inches.
' It will round the fractional inches to the nearest 1/x where x is the
denominator.

Denominator = 8 ' must be 2, 4, 8, 16, 32, 64, 128, etc.
NbrFeet = Int(Feetin)
InchIn = (Feetin - NbrFeet) * 12
NbrInches = Int(InchIn)
FracIn = (InchIn - NbrInches) * Denominator
Numerator = Application.WorksheetFunction.Round(FracIn, 0)
If Numerator = 0 Then
FracText = ""
ElseIf Numerator = Denominator Then
NbrInches = NbrInches + 1
FracText = ""
Else
Do
' If the numerator is even, divide both numerator and divisor by 2
If Numerator = Application.WorksheetFunction.Even(Numerator) Then
Numerator = Numerator / 2
Denominator = Denominator / 2
Else
FracText = " " & Numerator & "/" & Denominator
Exit Do
End If
Loop
End If
lentext = NbrFeet & "' " & NbrInches & FracText & """"
End Function

 

[Andrew Fergus]

Hi Dave and welcome to MrExcel.

Two questions :

What decimal units will be entered? Millimetres?

And what do you want to see as the output? Something like '3 feet, 2 inches'? Or something else?

Andrew

 

[dkotula]

Andrew

I want to take decimal feet and make them feet and inches.
example: Decimal: 21.27
Length: 21' 3 1/4"

Thanks
Dave

 

[Andrew Fergus]

Hi Dave
If you open your form in design view, right click the text box where you the user enters the decimal value -> Properties -> Event Tab -> After Update -> click the 3 dots to the right which will take you into the VBA editor screen. Copy and paste the following code into the VBA editor screen :

Private Sub txtDecimal_AfterUpdate()

Dim inputFeet As Double, _
    resultFeet As Integer, _
    tmpInches As Double, _
    resultInches As Integer, _
    resultRemainder As Integer, _
    Denominator As Integer, _
    Finished As Boolean, _
    strOutput As String
    
Finished = False
Denominator = 64 'use either 2, 4, 8, 16, 32, 64 etc.
inputFeet = Me.txtDecimal
resultFeet = Int(Abs(inputFeet))
tmpInches = 12 * (Abs(inputFeet) - resultFeet)
resultInches = Int(tmpInches)
resultRemainder = Denominator * (tmpInches - resultInches)

If resultRemainder < 1 Then
    resultRemainder = 0
    Finished = True
ElseIf resultRemainder > (Denominator - 1) Then
    resultInches = resultInches + 1
    resultRemainder = 0
    Finished = True
Else
    Do While Finished = False
        If (Denominator > 3) And ((resultRemainder Mod 2) = 0) Then
            resultRemainder = resultRemainder / 2
            Denominator = Denominator / 2
        Else
            Finished = True
        End If
    Loop
End If

strOutput = resultFeet & "' "
If resultInches > 0 Then
    strOutput = strOutput & resultInches
End If

If resultRemainder > 0 Then
    strOutput = strOutput & " " & resultRemainder & "/" & Denominator & """"
Else
    If resultInches > 0 Then
        strOutput = strOutput & """"
    Else
        strOutput = strOutput & "0"""
    End If
End If

If inputFeet < 0 Then
    strOutput = "-" & strOutput
End If

Me.txtLength = strOutput

End Sub

Save and close the VBA editor, and assuming you gave me the correct names, then this should work just fine.

HTH, Andrew

 

[dkotula]

Measurement conversion

It's not ouputting to txtLength. I'll go over everything and make sure the spellings and everything looks good.
Dave

 

[dkotula]

Does it matter that txtDecimal is not bound to anything, but txtLength is?

 

[dkotula]

Andrew,

I got it to work, I remved it and re-entered it. Works great, Thank you
Dave

 

[Andrew Fergus]

Hi Dave

I'm pleased you got it to work. Unfortunately there is a small error in the code. Where a fraction results in an extra inch that results in an extra foot, the current result is displayed as something like 10' 12", when it should be 11' 0"

Change this piece of code:

ElseIf resultRemainder > (Denominator - 1) Then
    resultInches = resultInches + 1
    resultRemainder = 0
    Finished = True
Else

to this

ElseIf resultRemainder > (Denominator - 1) Then
    resultInches = resultInches + 1
    resultFeet = resultFeet + resultInches \ 12
    resultInches = resultInches Mod 12
    resultRemainder = 0
    Finished = True
Else

It will now handle this situation correctly.

Regards, Andrew

 

[dkotula]

Thank you for the correction, your a life saver.
Dave