VBA Code To Parse and Evaluate Various Feet and Inch Notations For Peer Review

[Kcd2018]

I needed a function to convert feet and inches into a decimal number (eg 5' 10 1/2" => 60.5 (inches)). I found various excel formulas and UDF while googling around but either found them to limiting or error prone.

I've cooked up the following RegExp based UDF to evaluate feet & inch strings and return decimal values. So far it's working fine. Being RegExp based it is quite simple code as the regex engine does most the work. I spent more time fleshing out the explanation comment for how the function is expected to work than I actually did writing the function.

I feel it's pretty robust in the different input styles it will evaluate. It'll evaluate decimals, fractions, different unit symbols, and negative values.

I wanted to post this here for others to be able to use but also mainly to get feedback. Am an amateur coder and less than amateur regexp'er so please if you can see any problems let me know.

'/*---------------------------------------------------------------------
'|  Method FtIn2Dec
'|
'|  Purpose: To convert user input strings in various feet & inch
'|      notations to decimal inches. Uses a regular expression
'|      to parse the input into it's numeric components then
'|      performes appropriate arithmitic on the components to return a
'|      single decimal value in inches. Optionally return value can be
'|      feet
'|
'|  Pre-condition:
'|      The regular expression engine must be referenced:
'|      Tools -> References -> Microsoft VBScript Regular Expressions 5.5
'|
'|      1.  If the first non space character in the string is a hyphen or
'|          a open parentheses then the result is interpretted as negative.
'|          A closing parentheses at the end of the input is optional and
'|          no effect on the result. Only leading parentheses will return
'|          a negative number
'|          - 12' <= valid
'|          (12') <= valid
'|          (12'  <= valid
'|          12')  <= valid but NOT interpreted as negative
'|
'|      2.  Input may have up to two values, one feet value and one inch
'|          value each.
'|      3.  Input may ommit a feet value or an inch value
'|      4.  An input of an empty string will return 0
'|      5.  Feet unit symbols are ft and '
'|          Inch unit symbols are in, ", and ''
'|      6.  Values without a unit symbol following it will be interpreted
'|          as inches
'|          6 1/2   <= valid interpreted as inches
'|          12' 6.5 <= valid, same as 12' 6.5"
'|
'|      7.  Feet values must always be followed by either ft or ' symbols
'|          12ft    <= valid
'|          12'     <= valid
'|          12      <= valid but interpreted as inches
'|          12 6.5" <= invalid
'|
'|      8.  A feet value must always be before the inch value
'|          12 ft 6 1/2 in <= valid
'|          6 1/2 in 12 ft <= invalid
'|
'|      9.  If input has both a feet and inch value then the feet value
'|          must be a whole number
'|          12' 6 1/2" <= valid
'|          12.5' 1/2" <= invalid
'|
'|      10.  If input is only a feet value then it may be entered as a whole
'|          number or a decimal number. Never as a fraction
'|          12'     <= valid
'|          12.75'  <= valid
'|          12 3/4' <= invalid
'|
'|      11. Inches may be entered as a whole number, a whole number and a
'|          fraction, just a fraction, or as decimal number
'|          6"     <= valid
'|          6 1/2" <= valid
'|          6.5"   <= valid
'|          13/2"  <= valid
'|
'|          Note: spaces are the only valid character to seperate whole number
'|          from a fraction's numerator
'|          12' 6 1/2" <=valid
'|          12' 6-1/2" <=invalid
'|
'|      12. Components of a fraction must all be whole numbers
'|          6 1/2"   <= valid
'|          6 1.5/2" <= invalid
'|
'|      13. Optionally may seperate feet and inches with a hyphen
'|          12'-6 1/2" <= valid
'|
'|          Note: If ommiting a feet value and there is still a hyphen
'|          before the inch value the result will be returned as negative
'|          12'-6"   <= valid equal to 150 (inches)
'|          -12'-6"  <= valid equal to -150 (inches)
'|          -6"      <= valid equal to -6 (inches)
'|          --6"     <= invalid
'|
'|
'|      14. Spacing between negative symbol, unit symbols, and divisors
'|          are all optional
'|          (   12 '   -     6 1  /2   " ) <= valid and equals (12'-6 1/2")
'|
'|      15. The following examples are valid and will all return
'|          150.5 (inches):
'|          12'-6 1/2"
'|          12' 6 1/2"
'|          12 ft 6.5 in
'|          12 ft 13/2 "
'|          150 1/2"
'|          150 1/2
'|          150.5
'|          12ft 6.5
'|          12.541666666'
'|
'|      16. An input that doesn't meet the conditions above will return
'|          an error
'|
'|      17. Note there are no range limits on the numerical components
'|          12' 43 13/4" <= valid equal to 190.25 (inches)
'|
'|  Post-condition: returns a double in inches unless specified
'|      to return feet
'|
'|  Parameters:
'|      str -- String -- The input string to be parsed
'|      returnInches -- Optional boolean -- If true (default) the function
'|          will return a decimal value in inches. If false it will return
'|          a value in feet.
'|
'|  Returns:  A double in inch units unless specified to be feet. Returns
'|      an error code if the input string was invalid.
'*-------------------------------------------------------------------*/
Public Function FtIn2Dec(ByVal str As String, Optional returnInches As Boolean = True) As Variant
On Error GoTo ErrHandler:
    Dim regEx As New RegExp 'Tools -> References -> Microsoft VBScript Regular Expressions 5.5
    Dim matches As MatchCollection
    
    If Trim(str & vbNullString) = vbNullString Then
        FtIn2Dec = 0#
        GoTo ExitHandler
    ElseIf IsNumeric(str) Then
        FtIn2Dec = CDbl(str)
        GoTo ExitHandler
    End If
    
    Dim pattern As String
    pattern = "^\s*(-|\()?\s*(?:(\d+\.\d*|\d*\.\d+)\s*(?:ft|')|(?:(\d+)\s*(?:ft|'))?\s*-?\s*(?:(\d*?)(?:\s*(\d+)\s*\/\s*(\d+))?|(\d*\.\d+|\d+\.\d*))\s*(?:in|""|'')?)\s*\)?\s*$"
    
    Set regEx = New RegExp
    
    With regEx
        .Global = True
        .MultiLine = False
        .IgnoreCase = True
        .pattern = pattern
        Set matches = .Execute(str)
    End With
    
    If matches.Count = 0 Then GoTo ErrHandler
    
    Dim feet_part As Double
    Dim inch_part As Double
    Dim inch_total As Double
    Dim denominator As Integer
    
    With matches(0)
        denominator = CNum(.SubMatches(5))
        If denominator = 0 Then denominator = 1
        feet_part = CNum(.SubMatches(1)) + CNum(.SubMatches(2))
        inch_part = CNum(.SubMatches(3)) + (CNum(.SubMatches(4)) / denominator) + CNum(.SubMatches(6))
    End With
    inch_total = feet_part * 12# + inch_part
    If Not IsEmpty(matches(0).SubMatches(0)) Then inch_total = -1 * inch_total
    
    If returnInches Then FtIn2Dec = inch_total Else FtIn2Dec = inch_total / 12#
    
ExitHandler:
    Set regEx = Nothing
    Set matches = Nothing
    Exit Function

ErrHandler:
    Debug.Print "Error converting feet & inches string to a decimal value."
    FtIn2Dec = CVErr(2015)
    Resume ExitHandler
    
End Function

'CDbl( ) on a blank string will throw an error. Needed this little helper
' function to quickly convert empty and blank string/variants to zero for
' the FtIn2Dec function.
Private Function CNum(ByVal str As Variant) As Double
    If IsNumeric(str) Then CNum = CDbl(str) Else CNum = 0#
End Function

Here is a link to the where I built the regex patternhttps://regex101.com/r/qY1TuK/7

 

[Kcd2018]

To the mod that moved this to the excel question forum: I apologize for putting it in the incorrect spot. Because this is vba specific and not excel or access specific I had just figured it should go in the General Discussion forum.