'Functions for feet & inch conversion & display for Excel 2003
'	m2ft -> converts meters to feet & fractional inches
'	i2ft -> converts decimal inches to feet & fractional inches
'	ft2i -> converts feet & frational inches to decimal inches
'Written by Dave Typinski, 2006
'In Excel 2003, use alt-F11 and paste code into a new VBA module
'
'
Function m2ft(meters As Variant, level As Variant) As Variant
    
    'function converts meters to feet and fractional inches with
    '1/(2^'level') accuracy; e.g., for 1/16th-inch accuracy, set level = 4.
    '
    'Usage --> m2ft(meters, level)
    'where
    'meters = measurement in meters to be converted to feet & inches
    'level = granularity of fractional inches to the nearest 1/(2^level)th of an inch
    '
    'E.g., m2ft(2.49,5) returns the string <8' 2-1/32"> (without the brackets)
    '
    'Dave Typinski, 2006
        
    If meters = 0 Then
    
        m2ft = "0" & Chr$(34)
        
    Else
        
        'convert meters to decmal inches
        inches = meters / 0.0254
        
        'find number of fractions per inch and per foot
        fracperinch = 2 ^ level
        fracperfoot = fracperinch * 12
            
        'find number of fracs in measurement
        fracs = Round(inches * fracperinch, 0)
        
        'find number of whole feet in measurement
        wholefeet = Int(fracs / fracperfoot)
            
        'find leftover fracs less than 1 foot
        fracsleft = fracs - wholefeet * fracperfoot
           
        'find whole inches
        wholeinches = Int(fracsleft / fracperinch)
        
        'find leftover fracs less than 1 inch
        fracsleft = fracs - wholefeet * fracperfoot - wholeinches * fracperinch
        
        'make proper fraction -- e.g., convert 4/16" to 1/4"
        While fracperinch > 2 And fracsleft Mod 2 = 0
             fracsleft = fracsleft / 2
             fracperinch = fracperinch / 2
        Wend
        
        'build output string
        If wholefeet = 0 Then
            'no whole feet, build inches only
            If wholeinches = 0 Then
                'no whole inches, build inch fraction only & add double hashmark
                out = CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
            Else
                'build whole inches, with fractional inches if needed
                out = CStr(wholeinches)
                If fracsleft = 0 Then
                    'no fractional inches, build only whole inches & add double hashmark
                    out = out & Chr$(34)
                Else
                    'farctional inches exist, build whole inches and inch fraction & add double hashmark
                    out = out & "-" & CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
                End If
            End If
        Else
            'build whole feet, add single hashmark, add inches
            out = CStr(wholefeet) & "' " & CStr(wholeinches)
            If fracsleft = 0 Then
                'no fractional inches, finish whole inches with double hashmark
                out = out & Chr$(34)
            Else
                'fractional inches exist, add fraction and finish with double hashmark
                out = out & "-" & CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
            End If
        End If
                
        'return the formatted output string
        m2ft = out
      
    End If
      
End Function
'
'
'
Function i2ft(inches As Variant, level As Variant) As Variant
    
    'function converts decimal inches to feet and frational inches.
    'level is fractional inch divisions = 2^level;
    'i.e., for 1/16th-inch accuracy, use level = 4.
    
    If inches < 0 Then
        negflag = 1
        inches = inches * -1
    End If
    
    If inches = 0 Then
    
        i2ft = "0" & Chr$(34)
        
    Else
    
        'round to nearest facth of an inch
        fracperinch = 2 ^ level
        fracperfoot = fracperinch * 12
            
        'number of facs in measurement
        fracs = Round(inches * fracperinch, 0)
        
        'feet
        wholefeet = Int(fracs / fracperfoot)
            
        'leftover fracs less than 1 foot
        fracsleft = fracs - wholefeet * fracperfoot
           
        'inches
        wholeinches = Int(fracsleft / fracperinch)
        
        'leftover fracs less than 1 inch
        fracsleft = fracs - wholefeet * fracperfoot - wholeinches * fracperinch
        
        'make proper fraction
        While fracperinch > 2 And fracsleft Mod 2 = 0
             fracsleft = fracsleft / 2
             fracperinch = fracperinch / 2
        Wend
        
        'build string
        If wholefeet = 0 Then
            If wholeinches = 0 Then
                If fracsleft = 0 Then
                    out = "0" & Chr$(34)
                Else
                    out = CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
                End If
            Else
                out = CStr(wholeinches)
                If fracsleft = 0 Then
                    out = out & Chr$(34)
                Else
                    out = out & "-" & CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
                End If
            End If
        Else
            out = CStr(wholefeet) & "' " & CStr(wholeinches)
            If fracsleft = 0 Then
                out = out & Chr$(34)
            Else
                out = out & "-" & CStr(fracsleft) & "/" & CStr(fracperinch) & Chr$(34)
            End If
        End If
        
        If negflag = 1 Then
            out = "-" & out
        End If
        
        'return it
        i2ft = out
      
    End If
      
End Function
'
'
'
Function ft2i(ft As Variant) As Variant
    
    'function converts text feet and frational inches to decimal inches.
    'input string must have the form f' i-n/d"
    ' where
    ' f is an integer representing whole feet
    ' i is an integer >=0 representing whole inches, must be present (use 0 if necessary)
    ' n is an integer >=0 representing the numerator of fractional inches, optional
    ' d is an integer >0 representing the denominator of fractional inches, optional
            
    If Left(ft, 1) = "-" Then
        negflag = 1
        ft = Right(ft, Len(ft) - 1)
    End If
        
    'get position of feet separator
    s1 = InStr(1, ft, Chr(39), 1) 'find single quote mark / apostrophe
        
    'get position of whole inches separator
    s2 = InStr(1, ft, Chr(45), 1) 'find hyphen
        
    'get position of fractional inches numerator separator
    s3 = InStr(1, ft, Chr(47), 1) 'find slash
        
    'get position of fractional inches denominator separator
    s4 = InStr(1, ft, Chr(34), 1) 'find double quote mark
    
    'calculate whole feet
    If s1 > 0 Then
        out = CDbl(Mid(ft, 1, s1 - 1)) * 12
    Else
        out = 0
    End If
    
    'add whole inches
    If s2 > 0 Then 'hyphen is present in input string
        
        out = out + CDbl(Mid(ft, s1 + 1, s2 - s1 - 1))
        out = out + (CDbl(Mid(ft, s2 + 1, s3 - s2 - 1)) / CDbl(Mid(ft, s3 + 1, s4 - s3 - 1)))
    
    Else 'hyphen is not present in input string
    
        out = out + CDbl(Mid(ft, s1 + 1, s4 - s1 - 1))
    
    End If
    
    If negflag = 1 Then
        out = out * -1
    End If
    
    'return it
    ft2i = out
      
End Function