5 Replies Latest reply on Mar 3, 2017 6:14 PM by Elmar Klammer

    Convert Decimal value to Fractions

    JOHN GEORGE

      I have a macro to convert decimals to fractions and below is the function code

      This returns the value from decimal to fraction as shown:

      0.984 x 4.724 x 5.512   to  0'-0-63/64" x 0'-4-47/64" x 0'-5-33/64"

      Can someone please help me to modify this code so that if "feet" value is less than 1 I would like to eliminate the 0' and show only inches to show 0-63/64" x 4-47/64" x 5-33/64"

      Thanks

       

      Function DecimalToFeetInches(DecimalLength As Variant, Denominator As Integer) As String
        ' converts decimal inches to feet/inches/fractions
        Dim intFeet As Integer
        Dim intInches As Integer
        Dim intFractions As Integer
        Dim FractToDecimal As Double
        Dim remainder As Double
        Dim tmpVal As Double
        
        ' compute whole feet
        intFeet = Int(DecimalLength / 12)
        remainder = DecimalLength - (intFeet * 12)
        tmpVal = CDbl(Denominator)
      
        intInches = Int(remainder)
        remainder = remainder - intInches
      
        If Not (remainder = 0) Then
          If Not (Denominator = 0) Then
            FractToDecimal = 1 / tmpVal
              If FractToDecimal > 0 Then
                intFractions = Int(remainder / FractToDecimal)
                If (remainder / FractToDecimal) - intFractions > 0 Then
                  intFractions = intFractions + 1
                End If
              End If
           End If
        End If
        Call FractUp(intFeet, intInches, intFractions, Denominator) ' Simplify up & down
        DecimalToFeetInches = LTrim$(Str$(intFeet)) & "'-"
        DecimalToFeetInches = DecimalToFeetInches & LTrim$(Str$(intInches))
        If intFractions > 0 Then
          DecimalToFeetInches = DecimalToFeetInches & "-"
          DecimalToFeetInches = DecimalToFeetInches & LTrim$(Str$(intFractions))
          DecimalToFeetInches = DecimalToFeetInches & "/" & LTrim$(Str$(Denominator))
        End If   
        DecimalToFeetInches = DecimalToFeetInches & Chr$(34)    
      End Function
      
        • Re: Convert Decimal value to Fractions
          Peter Brinkhuis

          The function should return a string according to the first line. In the code you set the function name to a certain value to return that value. In line 30 to 37 the string DecimalToFeetInches is being set, so this part:

           

          DecimalToFeetInches = LTrim$(Str$(intFeet)) & "'-"

            DecimalToFeetInches = DecimalToFeetInches & LTrim$(Str$(intInches))

            If intFractions > 0 Then

              DecimalToFeetInches = DecimalToFeetInches & "-"

              DecimalToFeetInches = DecimalToFeetInches & LTrim$(Str$(intFractions))

              DecimalToFeetInches = DecimalToFeetInches & "/" & LTrim$(Str$(Denominator))

            End If

           

            DecimalToFeetInches = DecimalToFeetInches & Chr$(34)

           

          The first line is this section (line 30 in the original) prints the first section, the number of feet, a quote and a dash. You can make this optional with an if statement:

           

          If IntFeet <> 0 Then

            DecimalToFeetInches = LTrim$(Str$(intFeet)) & "'-"

          End If

           

          This might give you problems when the next line concatenates an empty string with other text, but VBA isn't very strict so it'll probably work. If it doesn't, initialize the string to "" at the declaration.

            • Re: Convert Decimal value to Fractions
              JOHN GEORGE

              Peter,

              Thanks for your help and really appreciate your suggestion

              Just another thought.. Is it possible to return the result without the 0 in 0-63/64 (my mistake in my first post)

              Same as feet, if the value is less than 1 inch, it should show only 63/64"

                • Re: Convert Decimal value to Fractions
                  Peter Brinkhuis

                  I'll help you one more time, if you promise me you'll stop using freedom units in the near future

                   

                  Function DecimalToFeetInches(DecimalLength As Variant, Denominator As Integer) As String
                    ' converts decimal inches to feet/inches/fractions
                    Dim intFeet As Integer
                    Dim intInches As Integer
                    Dim intFractions As Integer
                    Dim FractToDecimal As Double
                    Dim remainder As Double
                    Dim tmpVal As Double
                    
                    ' compute whole feet
                    intFeet = Int(DecimalLength / 12)
                    remainder = DecimalLength - (intFeet * 12)
                    tmpVal = CDbl(Denominator)
                  
                    intInches = Int(remainder)
                    remainder = remainder - intInches
                  
                    If Not (remainder = 0) Then
                      If Not (Denominator = 0) Then
                        FractToDecimal = 1 / tmpVal
                          If FractToDecimal > 0 Then
                            intFractions = Int(remainder / FractToDecimal)
                            If (remainder / FractToDecimal) - intFractions > 0 Then
                              intFractions = intFractions + 1
                            End If
                          End If
                       End If
                    End If
                    Call FractUp(intFeet, intInches, intFractions, Denominator) ' Simplify up & down
                    If intFeet <> 0 Then
                      DecimalToFeetInches = LTrim$(Str$(intFeet)) & "'-"
                    End If
                  
                    If (DecimalToFeetInches <> "" Or intInches <> 0) Then
                      DecimalToFeetInches = DecimalToFeetInches & LTrim$(Str$(intInches))
                      If intFractions <> 0 Then
                        DecimalToFeetInches = DecimalToFeetInches & "-"
                      End If
                    End If
                  
                  
                    If intFractions > 0 Then
                      DecimalToFeetInches = DecimalToFeetInches & LTrim$(Str$(intFractions))
                      DecimalToFeetInches = DecimalToFeetInches & "/" & LTrim$(Str$(Denominator))
                    End If
                    DecimalToFeetInches = DecimalToFeetInches & Chr$(34)
                  End Function
                  
              • Re: Convert Decimal value to Fractions
                Elmar Klammer

                Download the excel workbook from link below. The module Josh has put together works very well. Good learning tool as well.

                InchCalc Add-in for Excel

                 

                Below the formula to round to 1/16" using the Josh's i2s function. Change 0.0625 to 1/X (X being the rounding tolerance . In this case 1/16 = 0.0625) If you want 1/8 replace with 0.125. Fractions have never been easier. Great tool

                 

                0.60'-0 5/8"i2s(SUBSTITUTE(MROUND(A1,0.0625),CHAR(34),""),1)