Decimal to Fraction Conversion Excel Function

1. Copy the code listed below by highlighting the entire code including the Function and End Function statements, then selecting 'Edit', 'Copy' from the menus at the top of the screen or press CTRL-C.
2. Open Excel.
3. In Excel, press SHIFT-F11 to go to Visual Basic for Applications.
4. In Visual Basic for Applications (VBA), click Insert from the menu at the top of the page then click Module.
5. In VBA, paste the code you copied in step 1 by pressing CTRL-V or selecting 'Edit'-'Paste' from the menus at the top of the screen.
6. In VBA, press CTRL-S to save the function in your workbook.
7. In VBA, press ALT-Q to exit VBA and return to your workbook.
8. In Excel, press SHIFT-F3 to enter a function and select Module1.dec2frac().
   Module1 may be listed as something else depending on how it was saved from VBA

Function dec2frac(dblDecimal As Double) As String
'
'   Excel function to convert decimal values to integer fractions.
'
'   Written by:     Erik Oosterwal
'   Started on:     November 16, 2006
'   Completed on:   November 16, 2006
'

    Dim intNumerator, intDenominator, intNegative As Long   ' Declare integer variables as long
                                                            '   integers.
    Dim dblFraction, dblAccuracy As Double                  ' Declare floating point variables
                                                            '   as double precision.
    Dim txtDecimal As String                                ' need a string representation of the input value
                                                            ' in order to determine the required accuracy.
        
' Find the accuracy needed for the output by checking the number of digits behind the decimal point
'   of the input value.
'
'    dblAccuracy = 1 / 10 ^ (Len(CStr(dblDecimal - Fix(dblDecimal))) - 2)
'
'   While the formula above should work, there is a serious error in the way Excel handles
'   decimal numbers and there's a huge rounding error issue.  Subtracting the int() of
'   12.1 from 12.1 produces 0.0999999999 or something similar.  Obviously that won't
'   work for our desired accuracy of the magnitude of the fractional part of the number
'   so a slower more cumbersome method has to be used...
    
    dblAccuracy = 0.1                                       ' Set the initial Accuracy level.
    txtDecimal = CStr(dblDecimal)                           ' Get a  string representation of the input number.
    
    For i = 1 To Len(txtDecimal)                                ' Check each character to see if it's a decimal point...
        If Mid$(txtDecimal, i, 1) = "." Then                    ' if it is then we get the number of digits behind the decimal
            dblAccuracy = 1 / 10 ^ (Len(txtDecimal) - i + 1)    '   assign the new accuracy level, and
            Exit For                                            '   exit the for loop.
        End If
    Next
    
    intNumerator = 0                                ' Set the initial numerator value to 0.
    intDenominator = 1                              ' Set the initial denominator value to 1.
    intNegative = 1                                 ' Set the negative value flag to positive.
    
    If dblDecimal < 0 Then intNegative = -1         ' If the desired decimal value is negative,
                                                    '   then set the negative value flag to
                                                    '   negative.
    
    dblFraction = 0                                 ' Set the fraction value to be 0/1.
        
    While Abs(dblFraction - dblDecimal) > dblAccuracy   ' As long as we're still outside the
                                                        '   desired accuracy, then...
        If Abs(dblFraction) > Abs(dblDecimal) Then      ' If our fraction is too big,
            intDenominator = intDenominator + 1         '   increase the denominator
        Else                                            ' Otherwise
            intNumerator = intNumerator + intNegative   '   increase the numerator.
        End If
        
        dblFraction = intNumerator / intDenominator     ' Set the new value of the fraction.
        
    Wend
    
    dec2frac = LTrim(Str(intNumerator)) & "/" & LTrim(Str(intDenominator)) ' Display the numerator and denominator
    
End Function

Once you return to Excel, you can use this function just like you would any other math function, like =sum() or =avg(), you just have to remember to preface it with 'Module1'.  The formula in your cell would look something like:

                =module1.dec2frac(B7)

This will convert the value stored in cell B7 from a decimal value to a fraction.  The output from this function is a string but the actual value in the cell will be the reference to the function.  If you copy that cell to another cell you will get another reference to that function.  To get the actual fraction you need to use the Copy and Paste Special commands with 'Values' selected in the Paste Special window.