Excel functions to understand feet and inches input

Option Explicit

Public Function FT_DECI(Feet_STRING As String) As Variant
    Dim WORK_VARIANT
    Dim I As Integer
    Dim nchar As String
    Dim IsNegative As Boolean
    ''INITIALIZE WORK_VARIANT
    WORK_VARIANT = ""
    Feet_STRING = Trim(Feet_STRING)
   
    ''strip off leading alpha characters
    For I = 1 To Len(Feet_STRING)
        Select Case Mid(Feet_STRING, I, 1)
            Case "0" To "9"
                Exit For
            Case "-"
                IsNegative = True
        End Select
    Next I
   
    Feet_STRING = Trim(Right(Feet_STRING, Len(Feet_STRING) - I + 1))
   
    ''cleanup format string
    For I = 1 To Len(Feet_STRING)
        nchar = UCase(Mid(Feet_STRING, I, 1))
        Select Case nchar
            Case "0" To "9", "'", "."  '''''''<<<<<<<<<<<<<DEBUG "." Added
                WORK_VARIANT = WORK_VARIANT & nchar
            Case "\"
                WORK_VARIANT = WORK_VARIANT & "/"
            Case "F"
                WORK_VARIANT = WORK_VARIANT & "'"     ''replace starting f or ft or feet with ' mark
            Case "I", """"
                If Right(WORK_VARIANT, 1) <> """" Then WORK_VARIANT = WORK_VARIANT & """"
            Case " ", "-", "/"
                If Len(WORK_VARIANT) > 0 _
                   And Right(WORK_VARIANT, 1) <> " " _
                Then
                   WORK_VARIANT = WORK_VARIANT & " "
                End If
        End Select
    Next
   
    '''rightmost should be a double quote
    ''If Right(WORK_VARIANT, 1) <> """" Then WORK_VARIANT = WORK_VARIANT & """"
    If InStr(1, WORK_VARIANT, "'") > 0 Then
        WORK_VARIANT = Split(WORK_VARIANT, "'")             'split out feet and inches
        If UBound(WORK_VARIANT) > 1 Then GoTo FT_DECI_ERR   ''if too many ft references error out
        ''set whole feet
        FT_DECI = Val(WORK_VARIANT(0))  ''set feet
        WORK_VARIANT = WORK_VARIANT(1)  ''set remainder
       
    End If
   
    ''format remaining fraction
    WORK_VARIANT = Split(Trim(WORK_VARIANT), " ")
   
    ''check to see if there are too many placeholders - if no FEET have been defined we are ok.
    ''*note: split uses base-Zero arrays
    If UBound(WORK_VARIANT) > 2 And FT_DECI > 0 Then GoTo FT_DECI_ERR
    ''if feet are not defined we can have 4 placeholders
    If UBound(WORK_VARIANT) > 3 Then GoTo FT_DECI_ERR
   
    Select Case UBound(WORK_VARIANT)
        Case 1 ''two entries assumed to be fractional inches
            FT_DECI = FT_DECI + Val(WORK_VARIANT(0)) / Val(WORK_VARIANT(1)) / 12
        Case 2 '' three entries assumes whole and fractional inches
            FT_DECI = FT_DECI + (Val(WORK_VARIANT(0)) + Val(WORK_VARIANT(1)) / Val(WORK_VARIANT(2))) / 12
        Case 3 '' if deci_ft is still 0 then no feet have been set so assumes ft inch fractional inch format
            FT_DECI = Val(WORK_VARIANT(0)) + (Val(WORK_VARIANT(1)) + Val(WORK_VARIANT(2)) / Val(WORK_VARIANT(3))) / 12
        Case Else
            GoTo FT_DECI_ERR
    End Select

GoTo FT_DECI_EXIT
FT_DECI_ERR:
    FT_DECI = CVErr(xlErrNA)
    Exit Function
FT_DECI_EXIT:
    If IsNegative Then FT_DECI = -FT_DECI
End Function

Public Function FtIn(FEET_DBL As Double, Optional Fractional_Denominator_Tolerance As Integer)
    ' This function will change a decimal number of feet to the text string
    ' representation of feet, inches, and fractional inches.
    ' ROUNDS BASED ON Fractional_Denominator_Tolerance
   
    Dim RMDR As Double                  '' REMAINDER OF DECIMAL FEET AS FEET, INCHES AND FRACTIONAL INCES ARE SUBTRACTED
    Dim FEET_INT  As Integer            '' INTEGER FEET
    Dim INCH_INT As Integer             '' INTEGER INCHES
    Dim INCH_NUMERATOR_INT As Integer   '' FRACTIONAL INCHES NUMERATOR
    Dim DENOMINATOR As Integer          '' DENOMINATOR FOR FRACTIONAL INCHES
    ''Denominator= must be 2, 4, 8, 16, 32, 64, 128, etc.
    RMDR = FEET_DBL

    Select Case Fractional_Denominator_Tolerance
        Case 2, 4, 8, 16, 32, 64, 128, 256  ''ADD MORE IF YOU WANT TO- NEEDS TO BE MULTIPLE OF 2
            DENOMINATOR = Fractional_Denominator_Tolerance
        Case 0                              ''UNSPECIFIED CASE - DEFAULT TO ROUNDING TO THE NEAREST 1/8"
            DENOMINATOR = 8
        Case Else
            DENOMINATOR = 1                 ''OTHERWISE ROUND TO THE NEAREST INCH
    End Select
   
    FEET_INT = Fix(RMDR)
    RMDR = (RMDR - FEET_INT) * 12 ''SUBTRACT WHOLE FEET- MULTIPLY FOR FRACTIONAL INCHES
    INCH_INT = Fix(RMDR)
    RMDR = (RMDR - INCH_INT) * DENOMINATOR
    INCH_NUMERATOR_INT = Fix(RMDR + 0.5) ''rounds with the .5 added
   
    If INCH_NUMERATOR_INT = DENOMINATOR Then ''ROUND BUMPED UP TO WHOLE INCH
        INCH_NUMERATOR_INT = 0  ''REMOVE THE FRACTIONAL INCHES (E.G. 8/8)
        INCH_INT = INCH_INT + 1 ''ADD IT TO THE INCHES
    End If
   
    ''PROPER FRACTIONAL conversion so 6/8 is 3/4
    Do While Fix(INCH_NUMERATOR_INT / 2) = INCH_NUMERATOR_INT / 2 And DENOMINATOR / 2 > 1
        INCH_NUMERATOR_INT = INCH_NUMERATOR_INT / 2
        DENOMINATOR = DENOMINATOR / 2
    Loop
   
    FtIn = FEET_INT & "' " & INCH_INT
       
    If INCH_NUMERATOR_INT > 0 Then  ''FRACTIONAL INCHES
        FtIn = FtIn & " " & INCH_NUMERATOR_INT & "/" & DENOMINATOR
    End If
    ''ADD INCHES MARK
     FtIn = FtIn & """"
End Function

Comments

Popular posts from this blog

Revit area plans adding new types and references (Gross and rentable)

Organization and management of data in Revit across omniclass, Masterformat, Uniclass, and Uniformat