Convert and format imperial distance (feet and inches) with high 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.25Of 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.0190217391304347826086956522The 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 FunctionNow, with this function, converting an expression is very easy. For example:
SomeExpression = "4 ft. 5-7/64 in."
DecimalInches = ParseFeetInches(SomeExpression)
' DecimalInches -> 53.109375As 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:
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 = 64A 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 FunctionConverting 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.0254First 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 FunctionThen 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 FunctionNotice 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.00000390625In 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:
- as a Microsoft Access 2016 project: Rounding 1.3.2.accdb
- as a Microsoft Excel 2016 workbook: Rounding 1.3.2.xlsm
I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.
Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.
Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)