01005英制
转换英制距离 (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".
最常见的用法是用于英尺和英寸，例如2'8“，并且经常存在分数 ，例如： 3'5-3 / 4” 。
Such values you can convert to decimal inches using your head only:
您只能使用头部将这些值转换为十进制英寸 ：
2 feet is 2 * 12 inches = 24 inches
2英尺是2 * 12英寸= 24英寸
8 inches = 8 inches
8英寸= 8英寸
3/4 inches = 0.75 inches
3/4英寸= 0.75英寸
for a total of 32.75 inches. Easy enough.
总共32.75英寸。 很简单。
But how about a value of 332' 7-14549/65536"?
但是332'7-14549 / 65536“的值呢？
It's not a simple task to figure out its decimal value of 3991.22200012207 inches.
找出其3991.22200012207英寸的十进制值并非易事 。
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:
可以快速拆分一个简单的值，例如28.125英寸：
2 * 12 inches = 24 inches = 2 feet
2 * 12英寸= 24英寸= 2英尺
28 - 24 = 4 inches
28-24 = 4英寸
0.125 = 1/8 inches
0.125 = 1/8英寸
and the final expression is 2' 4-1/8".
最后的表达式是2'4-1 / 8“ 。
However, it takes at least pen and paper to convert, say, 3523.5602.
但是，至少需要笔和纸来转换，例如3523.5602 。
With some rounding (see later), the result will be 293' 7-18357/32768".
经过四舍五入 （请参阅下文），结果将为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.
在这里，我们将一个类似17'2-7 / 8“的表达式拆分成几部分，然后计算十进制值。
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“正常引号
1’ 5-1/4” Smart Quotes
1'5-1 / 4”智能报价
17 7/32"
17 7/32“
17 1/4 inches
17 1/4英寸
2 feet 3 inches
2英尺3英寸
2 feet and 3 7/32 inches
2英尺和3 7/32英寸
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.
幸运的是，VBA包含一个“小丑”函数Eval ，它可以执行向导操作，因为它可以采用以字符串形式创建的公式，并可以使用通用数学对其求值。
For example, the expression 1' 5-1/4" we can figure out equals 1 * 12 + 5 + 1/4 = 17.25.
例如，表达式1'5-1 / 4“我们可以算出等于1 * 12 + 5 + 1/4 = 17.25 。
To achieve this, we replace:
为此，我们替换：
' -> *12
'-> * 12
Space -> +
空格-> +
- -> +
--> +
" -> nothing (remove)
“->无（删除）
The result is the string "1*12+5+1/4" which we pass to Eval:
结果是字符串“ 1 * 12 + 5 + 1/4”，我们将其传递给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:
当然，必须准备更多的替代品-实际上是21种，而且有太多替代品无法进行全面讨论-但原则仍然是：
Replace any expected character or word with some math operator, and
用一些数学运算符替换任何期望的字符或单词，然后
remove everything else, so Eval can evaluate the final string.
删除所有其他内容，以便Eval可以评估最终字符串。
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.
此外，如在上面的示例表达式中，除法可以被包括为最后部分。 这必须分开处理，因为除法将迫使Eval返回Double，而Double的精度比Decimal小得多-后来转换为Decimal将无济于事。
An example will clearly illustrate, that using Double would reduce the count of decimals dramatically:
一个示例将清楚地说明，使用Double会大大减少小数位数：
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:
首先， 十进制值四舍五入为整数和小数本身就是一种练习。 实际上，上一篇文章：
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:
从那篇文章中，我们选择函数ConvertDecimalFractions ，它将为我们提供所需的输出：
' 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 如果英尺的总和为12或更大，则只有英尺
- Zero feet if total of inches is smaller than 12 如果总英寸数小于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.
那就是FormatFeetInches提供的功能。
It operates much the same way as Format, the native function of VBA, but has a few parameters more:
它的运行方式与VBA的本机功能Format相同，但是还有一些参数：
- 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"
f'ir“
which (using an exponent of 6) will return the value above as:
（使用6的指数）将返回上面的值：
4' 5-7/64"
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.
为此，还创建了两个其他功能： InchMeter和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.
对于“正常”值，它们也使用Decimal运算以获得极高的精度；对于非常小的值或非常大的值，仅使用Double运算。 因此，只要输入和输出都允许，它们将转换可以由VBA本地处理的任何数值。
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:
然后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:
当将这些零散的部分组合时，将4米转换为英寸，然后变成：
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
在这种情况下，实际的目的，你可能会轮值MetersOutput三个或四个小数-这将等于整数毫米或一毫米的十分之一，和圆形值到原来的4.0。 如果是这样，请参考上一篇文章：将值向上，向下，四舍五入或四舍五入
结论 (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.
对于极小的值或极大的值-超出数据类型Decimal范围的那些值-使用Double数据类型时，其精度较低。
进一步阅读 (Further reading )
More info on the power of two and rounding can be found at Wikipedia - Power of two
有关2的幂和取整的更多信息，请参见Wikipedia-2的幂
The previous article on rounding: Round by the power of two
上一篇关于舍入的文章： 通过二的幂进行舍入
资料下载 (Downloads )
The current version can always be found at GitHub.
当前版本始终可以在GitHub上找到 。
The initial version is here:
初始版本在这里：
- as a Microsoft Access 2016 project: Rounding 1.3.2.accdb 作为Microsoft Access 2016项目： 舍入1.3.2.accdb
- as a Microsoft Excel 2016 workbook: Rounding 1.3.2.xlsm 作为Microsoft Excel 2016工作簿： 取整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.
注意 ：如果您需要有关此主题的更多“支持”，请考虑使用Experts Exchange 的“提问”功能。 我会监督提出的问题，并很高兴与其他电子工程师一起为以这种方式提出的问题提供所需的任何其他支持。
Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.
如果您认为本文对EE成员有用且有价值，请不要忘记按下“竖起大拇指”按钮。
01005英制
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
