Excel Decimal FT and Decimal Ft-In
Decimal Feet and INches from ft-in string form Revit or AutoCAD
Attribute VB_Name = "FtIn"
Option Explicit
'''References:: Microsoft VB Script Regular Expressions 5.5
Const vbqt = """"
Const Pat_FtOnly = "(\d{1,})\'"
'' Feet '
Const Pat_FtIn = "(([\d\.]*)\'[\-\s]{0,4}){0,1}(([\d\.]{1,})[\" & vbqt & "\-\s]{1,3}){0,1}((\d{1,3})[\s\/\\]{1,3}(\d{1,3})[\" & vbqt & "]){0,1}"
''patterf Ft'-in "(\d{1,4})\'[\s\-]{1,3}(\d){1,2}[\s]{0,1}([\d\/]{3,7}){0,1}\" & vbqt
''Feet = (?:(\d?)(?:[\'\s\-]{1,4})){0,1}
''Inches = (?:(\d?)[\s\-\"]{0,3}){0,1}
''FractIn = (?:(\d{1,3})[\s\/\\]{1,3}(\d{1,3}))(?:[\"])
Function DecimalFT(strFtIn As String) As Variant ''Double
Dim RE As New RegExp
Dim reMa As MatchCollection
Dim rs As SubMatches
Dim dFT As Double ''Decimal Feet
Dim iIn As Integer ''Whole inches
Dim iInNumer As Integer ''Fractional Inches Numerator
Dim iInDenom As Integer ''Fractional Inches Denominator
With RE
.Global = False
.IgnoreCase = True
.MultiLine = False
.Pattern = Pat_FtIn
If .Test(strFtIn) = True Then
Set reMa = Nothing
Set reMa = RE.Execute(strFtIn)
''base-zero indexing!'''
''Check if all are empty return -1
If reMa.Item(0).SubMatches.Item(1) = Empty _
And reMa.Item(0).SubMatches.Item(3) = Empty _
And reMa.Item(0).SubMatches.Item(5) = Empty _
And reMa.Item(0).SubMatches.Item(6) = Empty _
Then
''DecimalFT = -1
DecimalFT = Error(744) ''Search Text Not found (Missing ' or ")
Exit Function
End If
dFT = Val(reMa.Item(0).SubMatches.Item(1))
iIn = Val(reMa.Item(0).SubMatches.Item(3))
iInNumer = Val(reMa.Item(0).SubMatches.Item(5))
iInDenom = Val(reMa.Item(0).SubMatches.Item(6))
End If
End With
DecimalFT = dFT + iIn / 12
If iInNumer > 0 And iInDenom > 0 Then
DecimalFT = DecimalFT + (iInNumer / iInDenom) / 12 ''Parenthesis for order of operations
End If
End Function
Function DecimalInches(strFtIn As String) As Double
DecimalInches = DecimalFT(strFtIn) * 12
End Function
Function ToleranceTest(strFtIn As String, StrFtInTolerance As String) As Boolean
''Mod throws errors with small fractions so we use 1/512" Unit counts
Dim a As Long 'Long integer in 1/512" Increments
Dim b As Long 'Long integer in 1/512" Increments
a = DecimalFT(strFtIn) * 12 * 512
b = DecimalFT(StrFtInTolerance) * 12 * 512
ToleranceTest = ((a Mod b) = 0)
End Function
Private Function Test_FtIn()
Debug.Print ToleranceTest("1/256" & vbqt, "1/4" & vbqt) '''outside clean dim tolerance (FALSE)
Debug.Print ToleranceTest("1-1/4" & vbqt, "1/4" & vbqt) ''' Meets clean dim tolerance (TRUE)
Debug.Print ToleranceTest("1-1/8" & vbqt, "1/4" & vbqt) '''Outside clean dim tolerance (FALSE)
Debug.Print DecimalFT("0.250000000000002") '''No ' or " specified - error out
Debug.Print DecimalFT("0.250000000000002'") '''Floating point export error from Revit
Debug.Print DecimalFT("3' - 2 1/4" & vbqt) ''3.1875 Feet
Debug.Print DecimalInches("1' 1" & vbqt) ''13.0 Inches
End Function
Comments
Post a Comment