New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Convert Decimals to Fractions like 1/2, n/4, n/8, n/16, n/32, n/64 (inches)

Free Excel Macros

Convert Decimals to Fractions like 1/2, n/4, n/8, n/16, n/32, n/64 (inches)

Postby Sisyphus » Tue May 22, 2012 4:10 am

You can display decimals as fractions in a worksheet by formatting the cell that contains the fraction. Unfortunately, the same method isn't available to show fractions in a TextBox. The following code will convert a decimal to the text string of a fraction which you can use for display purposes:
Code: Select all
Public Function ReducedFraction(ByVal Num As Double) As String

    Const HiD As Long = 64          ' Highest denominator
   
    Dim Rf As String
    Dim M As Long
    Dim n As Long
    Dim d As Long
   
    M = Int(Abs(Num))
    n = Round((Abs(Num) - M) * HiD, 0)
    If n Then
        If n = HiD Then
            M = M + 1
        Else
            d = HiD
            Do While (n Mod 2 = 0)
                n = n / 2
                d = d / 2
            Loop
            Rf = " " & CStr(n) & "/" & CStr(d)
        End If
    End If
    M = M * Sgn(Num)
    If Len(Rf) Then
        If M Then Rf = CStr(M) & Rf
    Else
        Rf = CStr(M)
    End If
    ReducedFraction = Rf
End Function

You can change the Const Hid (Highest Denominator) to any value that is divisible into 2. The lowest denominator returned by the function will be the first odd number encountered after repeatedly dividing the highest denominator into 2. For example
If HiD = 48 the divisions will produce the denominators 24, 12, 6 and 3.
If HiD = 64 the divisions will produce the denominators 32, 16, 8, 4 and 2.

Note that the original decimal will be rounded to the nearest HiD. For example 0.24 will return
15/64 if HiD = 64 but
1/4 if HiD = 32, 16, 8 or 4

The following code is an example of how to call the above function from within your code:
Code: Select all
Private Sub test()
    Debug.Print ReducedFraction(13.24)
End Sub
13.24 is the number to be converted. (The integer will be part of the returned string.) You can replace the actual number with any numeric variable or reference to a worksheet cell containing a number.
Have a great day! :D

Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)
Sisyphus
Former Moderator
 
Posts: 4454
Joined: Dec 7, 2011
Location: Shanghai
Reputation: 203
Excel Version: 2010

Return to Excel Macros

Who is online

Users browsing this forum: No registered users and 24 guests