Excel 2010 Feet-Inches [英] Excel 2010 Feet-Inches

问题描述

我真的希望Excel 2010会为我们提供一些将数字转换为英尺和英寸的内容。 任何人都看到了我错过的任何东西。 我知道VBA和InchCalc插件。 很高兴能从微软那里得到一些东西。 
似乎是一个需要的工具。

I was really hoping that Excel 2010 would give us something for converting a number to feet and inches.  Anyone seen anything that I have missed.  I'm aware of VBA and InchCalc add-in.  Would be nice to have something from Microsoft on this.  Seems to be a tool that is in demand.

推荐答案

创建一个模块并插入此代码，转到任何单元格A1 并输入12'-3 1/2"转到单元格A2并输入Cinches（B1）它将它转换为小数，

Create a module and insert this code, go to any cell A1 and type 12'-3 1/2" go to cell A2 and type Cinches(B1)it will convert it to a decimal,

转到单元格A2并键入CFeet（B1）它将转换回12 '-3 1/2"

go to cell A2 and type CFeet(B1) and it will converte back to 12'-3 1/2"

如果你或任何人想要这个并且不知道如何做它只是给我发私信我会给你一个链接，你可以下载一个样本。

if you or anyone wanting this and don't know how to do it just send me a private message i'll get you a link where you can download a sample.

顺便说一下代码不是我的，我很久以前就下载了它。它完美地工作。

by the way the code is not mine i downloaded it a long time ago it works perfectly.

Option Explicit


'\ This function converts a string like 5'-6 1/4" to a decimal number
'\ of inches that can be used in calculation.
Function CInches(Text_string_containing_values_for____Feet_Inches)

    '\ These values are used to examine the input string, one character at a time
    Dim vVal As String  '\ shorter name for input string
    Dim i As Integer    '\ counter to step through each character in input string
    Dim vChar As Variant '\ temporary storage of each input string character
    
    '\ These variables hold the values we discover for feet, inches and the
    '\ numerator and denominator of the fractional inches
    Dim iFt As Integer  '\ used to store number of feet
    Dim iIn As Integer  '\ number of inches
    Dim iNumerator As Integer   '\ numerator of fractional inches
    Dim iDenominator As Integer '\ denominator of fractional inches
    
    '\ In the process of discovering values for feet and inches, these variable
    '\ are used to accumulate and hold numeric values
    Dim iTemp As Integer    '\ Used to build a number as each digit is read
    
    '\ We want to ignore spaces, except for the very important space between
    '\ the number of inches and the numerator of the fractional inches
    '\ This variable is true if the last character processed was a space
    Dim bLastCharWasSpace As Boolean
   
    '\ First we assign input string to variable with shorter name
    vVal = Text_string_containing_values_for____Feet_Inches
    
    '\ If input string is numeric, then we don't want to convert it
    If IsNumeric(vVal) Then
        CInches = vVal
        Exit Function
    End If
    
    '\ Now we step through each character in input string from left to right
    iTemp = 0
    bLastCharWasSpace = False
    For i = 1 To Len(vVal)
        vChar = Mid(vVal, i, 1)
        
        '\ If character is a number, then we combine it with numbers before it
        '\ to get a number that we can assign to feet, inches, numerator or denominator
        If IsNumeric(vChar) Then
        
            '\ If this is a number and the last character was a space then
            '\ chances are, the number in iTemp is inches and we need to
            '\ start over building the numerator of fractional inches
            If bLastCharWasSpace = True And iIn = 0 Then
                    iIn = iTemp
                    iTemp = 0
            End If
            
            '\ As we read number from left to right, we multiply value of previous
            '\ number (if any) by 10 and add this number
            If iTemp = 0 Then
                iTemp = vChar
            Else
                iTemp = iTemp * 10
                iTemp = iTemp + vChar
            End If
        
        '\ The number we've been buiding must be feet
        ElseIf vChar = "'" Or vChar = "f" Then
            iFt = iTemp
            iTemp = 0
        
        '\ The number we've been bulding must be the numerator of fraction
        ElseIf vChar = "/" Then
            iNumerator = iTemp
            iTemp = 0
            
        '\ The number we've been building must be inches or
        '\ the denominator of the fraction, so we check to see if
        '\ there is a numerator
        ElseIf vChar = """" Or vChar = "i" Then
            If iNumerator > 0 Then
                iDenominator = iTemp
                iTemp = 0
            '\ If no numerator, then the number must be inches
            ElseIf iIn = 0 Then
                iIn = iTemp
                iTemp = 0
             End If
        End If
        
        '\ Now we set our indicator so that when we process the next
        '\ character, we will know if the last character was a space
        If vChar = " " Then
            bLastCharWasSpace = True
        Else
            bLastCharWasSpace = False
        End If
    Next i
    
    '\ To avoid dividing by zero if there was no numerator for fraction,
    '\ we set denominator to 1
    If iNumerator = 0 And iDenominator = 0 Then iDenominator = 1
    
    '\ Finally, we calculate number of decimal inches and return value
    CInches = (iFt * 12) + iIn + (iNumerator / iDenominator)
End Function


'\ This function converts a decimal number of inches to a text string like 5'-6 1/2"
Function CFeet(Decimal_Inches, Optional Enter_16_32_64_Etc__To_Round_Inches_To__Fraction_Of_Inch)
    '\ These variables are used to convert the decimal inches to the number
    '\ of fractional units. For example 6" would convert to 96 16ths
    Dim iNumUnits As Long '\ converted value   = 96 in example
    Dim iUnit As Double   '\ unit used to convert  = 1/16 in example
    
    '\ These varibles are used to hold calculated values that will become
    '\ part of the text string
    Dim iFeet As Integer
    Dim iInches As Integer
    Dim dFraction As Double
    Dim sFtSymbol As String
    
    '\ These variables are used to assign shorter names to input values
    Dim vVal As Variant
    Dim vDenominator As Variant
    
    '\ First we assign shorter names
    vVal = Decimal_Inches
    vDenominator = Enter_16_32_64_Etc__To_Round_Inches_To__Fraction_Of_Inch
    
    '\ If no denominator value was supplied, we will round to 1/9999 of inch
    If IsMissing(vDenominator) Then
        iUnit = 1 / 9999
    Else
        iUnit = 1 / vDenominator
    End If
    
    '\ Now we calculate the number of fractional units in the input value
    '\ Example 6 inches = 96 16ths
    iNumUnits = (vVal / iUnit)
    
    '\ We prepare each part of text string
    iFeet = Fix(iNumUnits / (12 / iUnit))
    iInches = Fix((iNumUnits Mod (12 / iUnit)) * iUnit)
    dFraction = (iNumUnits Mod (1 / iUnit)) * iUnit
    If iFeet <> 0 Then sFtSymbol = "'-"
    
    '\ Finally we format and return text string
    CFeet = Application.Text(iFeet, "##") & sFtSymbol & Application.Text(iInches + dFraction, "# ##/##\""")
    
End Function

这篇关于Excel 2010 Feet-Inches的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！