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

Kcd2018

New Member
Joined
Feb 2, 2018
Messages
2
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.

Code:
'/*---------------------------------------------------------------------
'|  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
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Kcd2018

New Member
Joined
Feb 2, 2018
Messages
2
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,863
Messages
5,489,323
Members
407,686
Latest member
Chuck1960

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...
Top