<

Convert and format imperial distance (feet and inches) with high precision

Published on
3,231 Points
231 Views
Last Modified:
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.

Converting imperial distance

The most common usage is for feet and inches like 2' 8" and often a fraction is present, like: 3' 5-3/4".

Such values you can convert to decimal inches using your head only: 


2 feet is 2 * 12 inches = 24 inches

8 inches = 8 inches

3/4 inches = 0.75 inches


for a total of 32.75 inches. Easy enough.


But how about a value of 332' 7-14549/65536"

It's not a simple task to figure out its decimal value of 3991.22200012207 inches.


Converting in the reverse direction - from decimal inches to feet, inches, and fraction - is similar. 

A simple value like 28.125 inches can quickly be split:


2 * 12 inches = 24 inches = 2 feet

28 - 24 = 4 inches

0.125 = 1/8 inches


and the final expression is 2' 4-1/8".


However, it takes at least pen and paper to convert, say, 3523.5602.
With some rounding (see later), the result will be 293' 7-18357/32768".


Obviously, tools are needed to convert such values, indeed when they represent very large or very small values.


Parse feet, inches, and a fraction to a decimal

This is where we split an expression like 17' 2-7/8" in its parts and then calculate the decimal value. 

While the calculation is easy, the tricky part is to get hold of the parts, as such expressions come in many varieties, for example:


1' 5-1/4"    Normal quotes
1’ 5-1/4”    Smart Quotes
17 7/32"
17 1/4 inches
2 feet 3 inches
2 feet and 3 7/32 inches


and just about any combination hereof.


Luckily, VBA contains a "joker" function, Eval, which can do wizardry, as it can take a formula - created as a string - and evaluate it using common math. 

For example, the expression 1' 5-1/4" we can figure out equals 1 * 12 + 5 + 1/4 = 17.25.

To achieve this, we replace:


'    -> *12

Space -> +

-    -> +

"    -> nothing (remove)


The result is the string "1*12+5+1/4" which we pass to Eval:


DecimalInches = Eval("1*12+5+1/4")
' DecimalInches -> 17.25

Of course, much more replacements must be prepared for - in fact, 21, and too many to discuss in full - but the principle remains: 

Replace any expected character or word with some math operator, and 

remove everything else, so Eval can evaluate the final string.

Further, as in the example expression above, a division may be included as the last part. This must be taken care of separately, because a division will force Eval to return a Double, and a Double has much less precision than a Decimal - and a later conversion to a Decimal won't help. 


An example will clearly illustrate, that using Double would reduce the count of decimals dramatically:


Fraction = CDec(Eval("77/4048"))
Fraction -> 0.0190217391304348

Fraction = CDec("77") / CDec("4048")
Fraction -> 0.0190217391304347826086956522

The final function consists of two main parts: first the clean-up, then the calculation where the integer part and the fraction (decimal) part are added to build a normal decimal number.

Please study the in-line comments for the detailed operation:


' Parse a string for a value of feet and/or inches.
' The inch part can contain a fraction or be decimal.
' Returns the parsed values as decimal inches.
' For unparsable expressions, zero is returned.
'
' Maximum returned value is +/- 7922816299999618530273437599.
' Negative values will only be read as such, if the first
' non-space character is a minus sign.
'
' Smallest reliably parsed value is the fraction 1/2097152
' or the decimal value 0.000000476837158203125.
'
' 2018-04-13. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ParseFeetInches( _
    ByVal Expression As String) _
    As Variant
   
    Dim ReplaceSets(20, 1)  As String
    Dim ExpressionParts     As Variant
    Dim ExpressionOneParts  As Variant
    Dim Sign                As Variant
    Dim DecimalInteger      As Variant
    Dim DecimalFraction     As Variant
    Dim DecimalInches       As Variant
    Dim Index               As Integer
    Dim Character           As String
    Dim FeetInches          As String
    Dim ExpressionOne       As String
    Dim ExpressionOneOne    As String
    Dim ExpressionOneTwo    As String
    Dim ExpressionTwo       As String
    Dim Numerator           As Long
    Dim Denominator         As Long
    ' Read sign.
    Sign = Sgn(Val(Expression))
    ' Trim double spacing.
    While InStr(Expression, "  ") > 0
        Expression = Replace(Expression, "  ", " ")
    Wend
    ' Replace foot units.
    ReplaceSets(0, 0) = "feet"
    ReplaceSets(0, 1) = "'"
    ReplaceSets(1, 0) = "foot"
    ReplaceSets(1, 1) = "'"
    ReplaceSets(2, 0) = "ft."
    ReplaceSets(2, 1) = "'"
    ReplaceSets(3, 0) = "ft"
    ReplaceSets(3, 1) = "'"
    ReplaceSets(4, 0) = Chr(SmartSingleQuote)   ' Smart Quote: "’"
    ReplaceSets(4, 1) = "'"
    ReplaceSets(5, 0) = " '"
    ReplaceSets(5, 1) = "'"
    ' Replace inch units.
    ReplaceSets(6, 0) = "inches"
    ReplaceSets(6, 1) = """"
    ReplaceSets(7, 0) = "inch."
    ReplaceSets(7, 1) = """"
    ReplaceSets(8, 0) = "inch"
    ReplaceSets(8, 1) = """"
    ReplaceSets(9, 0) = "in."
    ReplaceSets(9, 1) = """"
    ReplaceSets(10, 0) = "in"
    ReplaceSets(10, 1) = """"
    ReplaceSets(11, 0) = Chr(SmartDoubleQuote)  ' Smart Quote: "”"
    ReplaceSets(11, 1) = """"
    ReplaceSets(12, 0) = "''"
    ReplaceSets(12, 1) = """"
    ' Replace decimal separator.
    ReplaceSets(13, 0) = ","
    ReplaceSets(13, 1) = "."
    ' Replace units with operators.
    ReplaceSets(14, 0) = """"
    ReplaceSets(14, 1) = ""
    ReplaceSets(15, 0) = "'"
    ReplaceSets(15, 1) = "*" & CStr(InchesPerFoot) & " "
    ' Remove divider spaces.
    ReplaceSets(16, 0) = " /"
    ReplaceSets(16, 1) = "/"
    ReplaceSets(17, 0) = "/ "
    ReplaceSets(17, 1) = "/"
    ' Replace disturbing characters with neutral operator.
    ReplaceSets(18, 0) = " "
    ReplaceSets(18, 1) = "+"
    ReplaceSets(19, 0) = "-"
    ReplaceSets(19, 1) = "+"
    ReplaceSets(20, 0) = "+"
    ReplaceSets(20, 1) = "+0"
   
    ' Add leading neutral operator.
    Expression = "+0" & Expression
    ' Apply all replace sets.
    For Index = LBound(ReplaceSets, 1) To UBound(ReplaceSets, 1)
        Expression = Replace(Expression, ReplaceSets(Index, 0), ReplaceSets(Index, 1))
    Next
    ' Remove any useless or disturbing character.
    For Index = 1 To Len(Expression)
        Character = Mid(Expression, Index, 1)
        Select Case Character
            Case "0" To "9", "/", "+", "*", "."
                FeetInches = FeetInches & Character
        End Select
    Next
       
    ' For unparsable expressions, return 0.
    On Error GoTo Err_ParseFeetInches
   
    ExpressionParts = Split(FeetInches, "/")
    If UBound(ExpressionParts) = 0 Then
        ' FeetInches holds an integer part only, for example, "+00+038*12+0+05".
        ' Evaluate the cleaned expression as is.
        DecimalInches = Sign * CDec(Eval(FeetInches))
    Else
        ' FeetInches holds, for example, "+00+038*12+0+05+03/2048+0".
        ' For a maximum of decimals, split it into two parts:
        '   ExpressionOne = "+00+038*12+0+05+03"
        '   ExpressionTwo = "2048+0"
        ' or Eval would perform the calculation using Double only.
        ExpressionOne = ExpressionParts(0)
        ExpressionTwo = ExpressionParts(1)
        ' Split ExpressionOne into the integer part and the numerator part.
        ExpressionOneParts = Split(StrReverse(ExpressionOne), "+", 2)
        ' Retrieve the integer part and the numerator part.
        '   ExpressionOneOne = "+00+038*12+0+05"
        '   ExpressionOneTwo = "03"
        ExpressionOneOne = StrReverse(ExpressionOneParts(1))
        ExpressionOneTwo = StrReverse(ExpressionOneParts(0))
       
        ' Extract numerator and denominator.
        If Trim(ExpressionOneOne) = "" Then
            ' No integer expression is present.
            ' Use zero.
            ExpressionOneOne = "0"
        End If
        Numerator = Val(ExpressionOneTwo)
        Denominator = Val(ExpressionTwo)
       
        ' Evaluate the cleaned expression for the integer part.
        DecimalInteger = CDec(Eval(ExpressionOneOne))
        ' Calculate the fraction using CDec to obtain a maximum of decimals.
        If Denominator = 0 Then
            ' Cannot divide by zero.
            ' Return zero.
            DecimalFraction = CDec(0)
        Else
            DecimalFraction = CDec(Numerator) / CDec(Denominator)
        End If
        ' Sum and sign the integer part and the fraction part.
        DecimalInches = Sign * (DecimalInteger + DecimalFraction)
    End If
   
Exit_ParseFeetInches:
    ParseFeetInches = DecimalInches
    Exit Function
   
Err_ParseFeetInches:
    ' Ignore error and return zero.
    DecimalInches = CDec(0)
    Resume Exit_ParseFeetInches
   
End Function

Now, with this function, converting an expression is very easy. For example:


SomeExpression = "4 ft. 5-7/64 in."
DecimalInches = ParseFeetInches(SomeExpression)
' DecimalInches -> 53.109375

As seen, the spelled-out feet-inches text expression is converted to a normal numeric value.


Formatting decimal inches as feet, inches, and fraction

This process may be thought of as a straight-forward operation, but it is not. That is for two reasons:


  • Not every decimal has an exact fraction as it equivalent 
  • Values can be formatted in multiple ways, and no standard exists


Rounding

First, the rounding of the decimal value to an integer and a fraction is an exercise in its own. In fact, a previous article:


Round by the power of two


dealt with only this. So, please study that page for the basics and the full explanation.


From that article, we pick the function, ConvertDecimalFractions, which will serve for us the output we need:


' Rounds and converts a decimal value to an integer and the fraction of an integer
' using 4/5 midpoint rounding, optionally rounding up or down.
'
' Rounding method is determined by parameter RoundingMethod.
' For rounding up or down, rounding of negative values can optionally be set to
' away-from-zero or towards-zero respectively by parameter RoundingAsAbsolute.
'
' Returns the rounded value as a decimal.
' Returns numerator and denominator of the fraction by reference.
'
' For general examples, see function RoundMidBase2, RoundUpBase2, and RoundDownBase2.
'
' Will, for example, convert decimal inches to integer inches and a fraction of inches.
' However, numerator and denominator of the fraction are returned by reference in the
' parameters Numerator and Denominator for the value to be formatted as text by the
' calling procedure.
'
' Example:
'   Value = 7.22
'   Exponent = 2    ' will round to 1/4.
'   Numerator = 0
'   Denominator = 0
'
'   Result = ConvertDecimalFractions(Value, Exponent, Numerator, Denominator)
'
'   Result = 7.25
'   Numerator = 1
'   Denominator = 4
'
'   Result = ConvertDecimalFractions(Value, Exponent, Numerator, Denominator, Up)
'
'   Result = 7.25
'   Numerator = 1
'   Denominator = 4
'
'   Result = ConvertDecimalFractions(Value, Exponent, Numerator, Denominator, Down)
'
'   Result = 7
'   Numerator = 0
'   Denominator = 0
'
' If negative, parameter Exponent determines the rounding of the fraction as
' 1 / 2 ^ Exponent with a maximum of 21 - or from 1 / 2 to 1 / 2097152.
' For inches, that is a range from 12.7 mm to about 12.1 nm.
'
' If zero or positive, parameter Exponent determines the rounding of the
' integer value with 2 ^ Exponent with a maximum of 21 - or from 1 to 2097152.
' For inches, that is a range from 25.4 mm to about 53.27 km.
'
' Also, se comments for the required functions:
'
'   RoundUpBase2
'   RoundMidBase2
'   RoundDownBase2
'
' 2018-04-05. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function ConvertDecimalFractions( _
    ByVal Value As Variant, _
    ByVal Exponent As Integer, _
    Optional ByRef Numerator As Long, _
    Optional ByRef Denominator As Long, _
    Optional RoundingMethod As rmRoundingMethod = Midpoint, _
    Optional RoundingAsAbsolute As Boolean) _
    As Variant
   
    Dim Number      As Variant
    Dim Fraction    As Variant
   
    ' Validate rounding method.
    Select Case RoundingMethod
        Case Up, Midpoint, Down
            ' OK.
        Case Else
            ' Use default rounding method.
            RoundingMethod = Midpoint
    End Select
   
    If Exponent <= 0 Then
        ' Integer rounding only.
        Select Case RoundingMethod
            Case Up
                Number = RoundUpBase2(Value, Exponent, RoundingAsAbsolute)
            Case Midpoint
                Number = RoundMidBase2(Value, Exponent)
            Case Down
                Number = RoundDownBase2(Value, Exponent, RoundingAsAbsolute)
        End Select
        Fraction = 0
        Numerator = 0
        Denominator = 0
    Else
        ' Rounding with fractions.
        Number = Fix(CDec(Value))
        Select Case RoundingMethod
            Case Up
                Fraction = RoundUpBase2(Value - Number, Exponent, RoundingAsAbsolute)
            Case Midpoint
                Fraction = RoundMidBase2(Value - Number, Exponent)
            Case Down
                Fraction = RoundDownBase2(Value - Number, Exponent, RoundingAsAbsolute)
        End Select
       
        If Fraction = 0 Or Abs(Fraction) = 1 Then
            ' Fraction has been rounded to 0 or +/-1.
            Numerator = 0
            Denominator = 0
        Else
            ' Calculate numerator and denominator for the fraction.
            Denominator = Base2 ^ Exponent
            Numerator = Fraction * Denominator
            ' Find the smallest denominator.
            While Numerator Mod Base2 = 0
                Numerator = Numerator / Base2
                Denominator = Denominator / Base2
            Wend
        End If
    End If
   
    ConvertDecimalFractions = Number + Fraction
   
End Function

Thus, passing a decimal value for inches to the function will return the building blocks for the fraction to create a nicely formatted string to display the value.


A few lines of code will demonstrate this using the value, we parsed above:


' Input:
Value = 53.109375
Exponent = 6    ' will round to 1/64.

' Variables:
Inches = 0
Numerator = 0
Denominator = 0

Inches = Fix(ConvertDecimalFractions(Value, Exponent, Numerator, Denominator))

' Output:
' Numerator = 7
' Denominator = 64

A simple formula could create a readable result:


InchesWithFraction = Inches & "-" & Numerator & "/" & Denominator & """"
' InchesWithFraction -> 53-7/64"


Formatting

So, while a simple formatting is possible, there will, for various scenarios, often exist a requirement for a specific presentation of the values, for example:


  • Feet and inches, or inches only
  • No fraction for a numerator of zero
  • No fraction at all
  • Dash or no dash between feet and inches
  • Only feet if total of inches is 12 or more
  • Zero feet if total of inches is smaller than 12
  • No inches if feet are displayed and inches are zero
  • No units
  • Units spelled out as ft, ft., or foot/feet and in, in., or inch/inches


and variations hereof.


Clearly, what is needed, is a function that allows for flexible formatting of the value for decimal inches.

That is what the function FormatFeetInches offers.


It operates much the same way as Format, the native function of VBA, but has a few parameters more:


  • The first parameter is the value to format
  • The second parameter is an expression that determines the layout of the output
  • The third parameter determines the rounding precision
  • The remaining parameter controls various details of the formatting


The "building blocks" or placeholders for the formatting expression are these:


f       foot value except zero.
F       foot value including zero.
i       inch value except zero.
I       inch value including zero.
r       fraction value except zero.
R       fraction value including zero.
'       foot unit.
"       inch unit.
ft      foot unit, short, spelled out.
in      inch unit, short, spelled out.
ft.     foot unit, short with dot, spelled out.
in.     inch unit, short with dot, spelled out.
foot    foot unit, long, spelled out.
inch    inch unit, long, spelled out.
/       fraction separator (divider)
<space> spacer.
-       dash.
\       escape character.

The default format expression is the quite common format:


    f' i-r"


which (using an exponent of 6) will return the value above as:


    4' 5-7/64"


Just about any reasonable formatting requirement can be met by combining these placeholders. The header comments of the function (see the next code block) list a lot of examples to illustrate the many options.


The function may appear overwhelming, but it is not that bad. It is heavily documented in-line, and is basically not convoluted - it rounds the value (determined by the optional parameters), then builds the foot part, the inch part, and the fraction part - and finally concatenates these:


' Rounds and formats a decimal value of inches to integer feet and inches and a fraction of inches
' applied either a default or a custom format.
'
' Parameter Exponent determines rounding. Rounds by default to integer inches.
' Parameter Format determines the format of the output. Default is: f' i-r"
' Parameter SmartQuotes will - if True, and if the output contains quotes - replace these quotes
' with "Smart Quotes" as used in Word.
'
' Parameters RoundingMethod determines the rounding method.
' Default is by 4/5, as it is for the native VBA.Format function.
' For rounding up or down, rounding of negative values can optionally be set to
' away-from-zero or towards-zero respectively by parameter RoundingAsAbsolute.
' Format placeholders:
'   f       foot value except zero.
'   F       foot value including zero.
'   i       inch value except zero.
'   I       inch value including zero.
'   r       fraction value except zero.
'   R       fraction value including zero.
'   '       foot unit.
'   "       inch unit.
'   ft      foot unit, short, spelled out.
'   in      inch unit, short, spelled out.
'   ft.     foot unit, short with dot, spelled out.
'   in.     inch unit, short with dot, spelled out.
'   foot    foot unit, long, spelled out.
'   inch    inch unit, long, spelled out.
'   /       fraction separator (divider)
'   <space> spacer.
'   -       dash.
'   \       escape character.
'
' Examples:
'   FormatFeetInches(17.222, 4)                         -> 1' 5-1/4"
'   FormatFeetInches(17.222, 4, , True)                 -> 1’ 5-1/4”    ' Smart Quotes.
'   FormatFeetInches(17.222, 4, "i-r")                  -> 17-1/4
'   FormatFeetInches(17.222, 4, "i-r""")                -> 17-1/4"
'   FormatFeetInches(17.222, 6, "i r""")                -> 17 7/32"
'   FormatFeetInches(7.222, 4, "f' i-r""")              -> 7-1/4"
'   FormatFeetInches(7.222, 4, "F' i-r""")              -> 0' 7-1/4"
'   FormatFeetInches(12.222, 4, "f' i-r""")             -> 1' 1/4"
'   FormatFeetInches(12.222, 4, "f' I-r""")             -> 1' 0-1/4"
'   FormatFeetInches(17.222, 0, "i-r""")                -> 17"
'   FormatFeetInches(17.222, 0, "i-R""")                -> 17-0/0"
'   FormatFeetInches(0.222, 2, "f' i-r""")              -> 1/4"
'   FormatFeetInches(0.222, 2, "F' i-r""")              -> 0' 1/4"
'   FormatFeetInches(12.222, 2, "f ft i r in")          -> 1 ft 1/4 in
'   FormatFeetInches(12.222, 0, "f ft i r in")          -> 1 ft
'   FormatFeetInches(12.222, 0, "f ft I r in")          -> 1 ft 0 in
'   FormatFeetInches(17.222, 2, "fft. I rin.")          -> 1 ft. 5 1/4 in.
'   FormatFeetInches(17.222, 2, "i r inches")           -> 17 1/4 inches
'   FormatFeetInches(1.222, 2, "i r inches")            -> 1 1/4 inches
'   FormatFeetInches(1.222, 0, "i r inches")            -> 1 inch
'   FormatFeetInches(17.222, 0, "i r inch")             -> 17 inches
'   FormatFeetInches(1.222, 0, "i r inch")              -> 1 inch
'   FormatFeetInches(27.222, 0, "f feet i r inches")    -> 2 feet 3 inches
'   FormatFeetInches(17.222, 0, "f feet i r inches")    -> 1 foot 5 inches
'   FormatFeetInches(7.222, 0, "F feet i r inches")     -> 0 feet 7 inches
'   FormatFeetInches(7.222, 2, "F feet i-r inches")     -> 0 feet 7-1/4 inches
'   FormatFeetInches(27.22, 6, "f foot and I r inch")   -> 2 feet and 3 7/32 inches
'
'   FormatFeetInches(17.222, 0, , , Up)                 -> 1' 6"
'   FormatFeetInches(17.222, 0, , , Down)               -> 1' 5"
'
' Also, se comments for the required functions:
'
'   ConvertDecimalFractions
'   RoundUpBase2
'   RoundMidBase2
'   RoundDownBase2
'
' 2018-04-12. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function FormatFeetInches( _
    ByVal Value As Variant, _
    Optional ByVal Exponent As Long, _
    Optional ByVal Format As String, _
    Optional ByVal SmartQuotes As Boolean, _
    Optional RoundingMethod As rmRoundingMethod = Midpoint, _
    Optional RoundingAsAbsolute As Boolean) _
    As String
   
    Const FootSymbol        As String = "f"
    Const InchSymbol        As String = "i"
    Const FractionSymbol    As String = "r"
    Const FootUnit          As String = "'"
    Const InchUnit          As String = """"
    Const FractionSeparator As String = "/"
    Const Spacer            As String = " "
    Const Dash              As String = "-"
    Const Escape            As String = "\"
   
    Const SingularFoot      As String = "foot"
    Const SingularInch      As String = "inch"
    Const PluralFoot        As String = "feet"
    Const PluralInch        As String = "inches"
    Const UniFoot           As String = "ft"
    Const UniInch           As String = "in"
    Const UniDotFoot        As String = UniFoot & "."
    Const UniDotInch        As String = UniInch & "."
       
    ' Default format: f' i-r"
    Const DefaultFormat     As String = FootSymbol & FootUnit & Spacer & InchSymbol & Dash & FractionSymbol & InchUnit
   
    Dim Numerator       As Long
    Dim Denominator     As Long
    Dim Feet            As Variant
    Dim AllInches       As Variant
    Dim Inches          As Variant
   
    Dim FootPart        As String
    Dim InchPart        As String
    Dim DashPart        As String
    Dim FractionPart    As String
    Dim FullPart        As String
    Dim Length          As Integer
    Dim Index           As Integer
    Dim Character       As String
    Dim LongFoot        As Boolean
    Dim LongInch        As Boolean
    Dim ShortFoot       As Boolean
    Dim ShortInch       As Boolean
    Dim ShortDotFoot    As Boolean
    Dim ShortDotInch    As Boolean
    If Not IsNumeric(Value) Then Exit Function
   
    If Format = "" Then
        Format = DefaultFormat
    End If
    ' Default spacer between integer inches and fraction of inches.
    DashPart = Spacer
    Length = Len(Format)
       
    ' Calculate the integer feet/inches and the fraction (remainder).
    AllInches = Fix(ConvertDecimalFractions(Value, Exponent, Numerator, Denominator, RoundingMethod, RoundingAsAbsolute))
    Feet = AllInches \ InchesPerFoot
    Inches = AllInches Mod InchesPerFoot
   
    ' Singularise spelled out long units.
    Format = Replace(Format, PluralFoot, SingularFoot)
    Format = Replace(Format, PluralInch, SingularInch)
    ' Temporarily replace all spelled out units with single character units.
    If InStr(1, Format, SingularFoot, vbTextCompare) > 1 Then
        LongFoot = True
        Format = Replace(Format, SingularFoot, FootUnit)
    ElseIf InStr(1, Format, UniDotFoot, vbTextCompare) > 1 Then
        ShortDotFoot = True
        Format = Replace(Format, UniDotFoot, FootUnit)
    ElseIf InStr(1, Format, UniFoot, vbTextCompare) > 1 Then
        ShortFoot = True
        Format = Replace(Format, UniFoot, FootUnit)
    End If
    If InStr(1, Format, SingularInch, vbTextCompare) > 1 Then
        LongInch = True
        Format = Replace(Format, SingularInch, InchUnit)
    ElseIf InStr(1, Format, UniDotInch, vbTextCompare) > 1 Then
        ShortDotInch = True
        Format = Replace(Format, UniDotInch, InchUnit)
    ElseIf InStr(1, Format, UniInch, vbTextCompare) > 1 Then
        ShortInch = True
        Format = Replace(Format, UniInch, InchUnit)
    End If
       
    ' Build parts.
    For Index = 1 To Length
        Character = Mid(Format, Index, 1)
        Select Case Character
            Case LCase(FootSymbol)
                If Feet > 0 Then
                    FootPart = CStr(Feet)
                Else
                    ' No display of feet.
                End If
            Case UCase(FootSymbol)
                ' Display any feet, even zero.
                FootPart = CStr(Feet)
            Case LCase(InchSymbol)
                If Inches > 0 Then
                    InchPart = CStr(Inches)
                Else
                    ' No display of inches.
                End If
            Case UCase(InchSymbol)
                ' Display any inches, even zero.
                InchPart = CStr(Inches)
            Case LCase(FractionSymbol)
                If Numerator > 0 Then
                    FractionPart = CStr(Numerator) & FractionSeparator & CStr(Denominator)
                Else
                    ' No display of fraction.
                End If
            Case UCase(FractionSymbol)
                ' Display any fraction, even when zero.
                FractionPart = CStr(Numerator) & FractionSeparator & CStr(Denominator)
            Case Dash
                ' Use a dash as spacer between integer inches and fraction of inches.
                DashPart = Dash
            Case Escape
                ' Skip the next character.
                Index = Index + 1
        End Select
    Next
   
    ' Adjust parts.
    If FootPart = "" Then
        If InchPart <> "" Then
            InchPart = CStr(AllInches)
        End If
    End If
    If InchPart = "" Or FractionPart = "" Then
        ' Not both integer inches and fraction of inches,
        ' thus no spacer between these.
        DashPart = ""
    End If
   
    ' Assemble parts.
    For Index = 1 To Length
        Character = Mid(Format, Index, 1)
        Select Case Character
            Case LCase(FootSymbol), UCase(FootSymbol)
                ' Append foot part.
                FullPart = FullPart & FootPart
            Case FootUnit
                ' Append foot unit if feet to display.
                If FootPart <> "" Then
                    ' Right-trim FullPart to remove space between value and unit.
                    FullPart = RTrim(FullPart) & FootUnit
                Else
                    ' No feet to display.
                End If
            Case LCase(InchSymbol), UCase(InchSymbol)
                ' Append inch part.
                FullPart = FullPart & InchPart
            Case InchUnit
                ' Append inch unit if inches to display.
                ' Right-trim FullPart to remove space between value and unit.
                If InchPart & FractionPart <> "" Then
                    FullPart = RTrim(FullPart) & InchUnit
                Else
                    ' No inches to display.
                End If
            Case LCase(FractionSymbol), UCase(FractionSymbol)
                ' Append fraction part.
                FullPart = FullPart & FractionPart
            Case Dash
                ' DashPart has been set in first loop.
                FullPart = FullPart & DashPart
            Case Spacer
                ' Right-trim FullPart to prevent double-spaces.
                FullPart = RTrim(FullPart) & Character
            Case Escape
                ' Skip this character and read the next literally.
                Index = Index + 1
                If Index <= Length Then
                    FullPart = FullPart & Mid(Format, Index, 1)
                End If
            Case Else
                ' Append any other character as is.
                FullPart = FullPart & Character
        End Select
    Next
   
    ' Restore spelled-out units.
    If LongFoot = True Then
        If Feet = 1 Then
            FullPart = Replace(FullPart, FootUnit, Spacer & SingularFoot)
        Else
            FullPart = Replace(FullPart, FootUnit, Spacer & PluralFoot)
        End If
    ElseIf ShortDotFoot = True Then
        FullPart = Replace(FullPart, FootUnit, Spacer & UniDotFoot)
    ElseIf ShortFoot = True Then
        FullPart = Replace(FullPart, FootUnit, Spacer & UniFoot)
    End If
    If LongInch = True Then
        If InchPart = "1" And Numerator = 0 Then
            FullPart = Replace(FullPart, InchUnit, Spacer & SingularInch)
        Else
            FullPart = Replace(FullPart, InchUnit, Spacer & PluralInch)
        End If
    ElseIf ShortDotInch = True Then
        FullPart = Replace(FullPart, InchUnit, Spacer & UniDotInch)
    ElseIf ShortInch = True Then
        FullPart = Replace(FullPart, InchUnit, Spacer & UniInch)
    End If
   
    If SmartQuotes = True Then
        ' Return output with "Smart Quotes".
        FullPart = Replace(FullPart, FootUnit, Chr(SmartSingleQuote))
        FullPart = Replace(FullPart, InchUnit, Chr(SmartDoubleQuote))
    End If
   
    FormatFeetInches = LTrim(FullPart)
   
End Function


Converting between meters and inches

Quite often a parsed foot/inch expression should result in a metric value, or - likewise - a metric value should be converted and formatted as an equivalent foot/inch expression. 

In other words, a decimal inch value will only be a temporary value that has been or will be converted to its equivalent metric value while preserving the precision.


For this purpose, two other functions have been created: InchMeter and MeterInch.


These also operate with Decimal for "normal" values to obtain extreme precision, and only with Double for very small or very large values. Thus, they will convert any numeric value that can be handled natively by VBA as long both input and output will allow.


They both use the fixed constant for the conversion ratio:


' Meter/inch relation. 1 inch = 0.0254 m.
Public Const MetersPerInch      As Currency = 0.0254

First InchMeter:


' Converts a value for a measure in meters to inches.
' Returns 0 (zero) for invalid inputs.
'
' Will convert any value within the range of Decimal
' with the precision of Decimal.
' Converts values exceeding the range of Decimal as
' Double.
' Largest value with full 28-digit precision is 1E+27
' Smallest value with full 28-digit precision is 1E-26
'
' Examples:
'   Meter = 4.0
'   Inch = InchMeter(Meter)
'   Inch -> 157.48031496062992125984251969
'
'   Meter = 2.54
'   Inch = InchMeter(Meter)
'   Inch -> 100.0
'
' 2018-04-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function InchMeter( _
    ByVal Value As Variant) _
    As Variant
    Dim Result  As Variant
   
    If IsNumeric(Value) Then
        On Error Resume Next
        Result = CDec(Value) / MetersPerInch
        If Err.Number <> 0 Then
            ' Decimal overflow.
            ' Calculate without conversion to Decimal.
            Result = CDbl(Value) / MetersPerInch
        End If
    Else
        Result = 0
    End If
   
    InchMeter = Result
   
End Function

Then MeterInch:


' Converts a value for a measure in inches to meters.
' Returns 0 (zero) for invalid inputs.
'
' Will convert any value within the range of Decimal
' with the precision of Decimal.
' Converts values exceeding the range of Decimal as
' Double.
'
' Largest value with full 28-digit precision is 1E+26
' Smallest value with full 28-digit precision is 1E-24
'
' Examples:
'   Inch = 40.0
'   Meter = MeterInch(Inch)
'   Meter -> 1.016
'
'   Inch = 1 / MetersPerInch            ' Double.
'   Inch -> 39.3700787401575
'   Meter = MeterInch(Inch)
'   Meter -> 1.0000000000000005
'
'   Inch = CDec(1) / MetersPerInch      ' Decimal.
'   Inch -> 39.370078740157480314960629921
'   Meter = MeterInch(Inch)
'   Meter -> 1.0
'
' 2018-04-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function MeterInch( _
    ByVal Value As Variant) _
    As Variant
    Dim Result  As Variant
   
    If IsNumeric(Value) Then
        On Error Resume Next
        Result = CDec(Value) * MetersPerInch
        If Err.Number <> 0 Then
            ' Decimal overflow.
            ' Calculate without conversion to Decimal.
            Result = CDbl(Value) * MetersPerInch
        End If
    Else
        Result = 0
    End If
   
    MeterInch = Result
   
End Function

Notice the in-line example showing the extreme precision that one meter will be converted with.


When combining the bits and pieces, converting, say 4 meters, to inches and back could look like:


MetersInput = 4
Inches = Formatfeetinches(InchMeter(MetersInput), 8)
' Inches -> 13' 1-123/256"

MetersOutput = MeterInch(ParseFeetInches(Inches))
' MetersOutput - > 4.00000390625

In this case, for a practical purpose, you would probably round the value MetersOutput to three or four decimals - which will equal integer millimeters or one-tenth of a millimeter, and round the value to the original 4.0. If so, refer to the previous article: Rounding values up, down, by 4/5, or to significant figures


Conclusion

The four functions presented here will allow you - for any practical purpose and many more rare purposes - to convert between decimal values of meters or inches and all the various common (and many uncommon) formats for feet and inches and fractions of inches.

The fractions are not limited to the common quite coarse fractions of inches; also tiny fractions that may equal the metric distance of a few nanometers can be converted with very high precision.

For extremely small or large values - those exceeding the range of data type Decimal - data type Double is used with its somewhat lower precision.


Further reading 

More info on the power of two and rounding can be found at Wikipedia - Power of two

The previous article on rounding: Round by the power of two


Downloads                         

The current version can always be found at GitHub.

The initial version is here:


0
Comment
0 Comments

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

Join & Write a Comment

Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month