[RESOLVED] decimals to fractions

[VB4fun]

Known: Excel stores values as decimals, even when it displays decimals.

Know: When you bring the value to a label on the form, it displays the decimal.

Unknown: Is there an easy way to make it show the same fraction displayed in excel?

[Webtest]

What is the range of formats in a particular cell on your Excel sheet? If you can force the format to a fixed format, you can use the same format for the text in your Label.

There is an Excel function to determine the format for any cell (see CELL function).

I think what you are trying to do is to take the identical text string representation of a decimal number that appears in the Excel sheet cell and put that same string in your label. Is this a correct interpretation?

[VB4fun]

I am not sure how the Excel function would help me with this issue, unless I am missing something.

think what you are trying to do is to take the identical text string representation of a decimal number that appears in the Excel sheet cell and put that same string in your label. Is this a correct interpretation?

Correct, a Label on a form.

Excel Cell A15 = 1/4"

Form Label lblFraction.caption = .25

Desired result lblFraction.caption = 1/4"

[DKenny]

Is the value in cell A15 always going to be a rational number? i.e. one that can be represented as a fraction.
If the value in the cell is irrational, like Pi or e, what would you like to display on the label?
Can the value in A15 evcer be greater than 1?

[VB4fun]

Actually, value of the cell is always going to be a measurement/dimension:
23 1/4, 34 5/8, 19 15/16.

There are times it may be whole:
27, 56, 82

What I would like displayed on the form, is what you see here, I can take care of what happens in Excel with functions, etc. for my calculations,etc.

[Webtest]

On your Excel sheet ... in a typical cell ... do you see 23 1/4 or do you see 23.25? If youi see "23 1/4" it must be a text string, and you should be able to just copy the text into the Label.

Is 1/16" the finest resolution you are dealing with, or are you using smaller fractions? What is the smallest fraction?

[VB4fun]

I see 23 1/4, because the format is set to that. Just because I see 23 1/4 does not mean it is a text string.

The smallest dimension is probably going to be 1"
The smallest fractional portion of the number is probably going to 1/32" (i.e. 24 1/32")

But not sure why this would have a bearing..?

[DKenny]

The attached function will give you a textual representation of a fractional value.
For irrational value it will only give an approximate fraction. The accuracy of that approximation can be increased by increasing the value of the Percision variable.

Function FractDisplay(MyDecimal As Double, Optional Precision As Integer = 100) As String
Dim Neg As Boolean
Dim Whole As Integer
Dim WholePart As String
Dim Numer As Integer
Dim Denom As Integer
Dim Frac As Double
Dim ChosenFrac As Double
    
    Neg = MyDecimal < 0
    
    MyDecimal = Abs(MyDecimal)
    Whole = Int(MyDecimal)
    
    Select Case CDbl(Whole)
        Case MyDecimal
            FractDisplay = CStr(Whole)
            GoTo NegCheck
        Case 0
            WholePart = ""
        Case Else
            WholePart = CStr(Whole) & " "
    End Select
    
    MyDecimal = MyDecimal - Whole
    
    ChosenFrac = 0
    For Denom = 2 To Precision
        For Numer = 1 To (Precision - 1)
            Frac = Numer / Denom
            If Frac = MyDecimal Then
                FractDisplay = WholePart & CStr(Numer) & "/" & CStr(Denom)
                GoTo NegCheck
            End If
            If Abs(Frac - MyDecimal) < Abs(ChosenFrac - MyDecimal) Then
                FractDisplay = WholePart & CStr(Numer) & "/" & CStr(Denom)
                ChosenFrac = Frac
            End If
        Next Numer
    Next Denom
 
NegCheck:
    If Neg Then FractDisplay = "-" & FractDisplay
End Function

[DKenny]

Sorry guys, one error in the above. The Numerator only need to loop through to the value of the Denominator, not the precision. Code below has been ammended.

Function FractDisplay(MyDecimal As Double, Optional Precision As Integer = 100) As String
Dim Neg As Boolean
Dim Whole As Integer
Dim WholePart As String
Dim Numer As Integer
Dim Denom As Integer
Dim Frac As Double
Dim ChosenFrac As Double
    
    Neg = MyDecimal < 0
    
    MyDecimal = Abs(MyDecimal)
    Whole = Int(MyDecimal)
    
    Select Case CDbl(Whole)
        Case MyDecimal
            FractDisplay = CStr(Whole)
            GoTo NegCheck
        Case 0
            WholePart = ""
        Case Else
            WholePart = CStr(Whole) & " "
    End Select
    
    MyDecimal = MyDecimal - Whole
    
    ChosenFrac = 0
    For Denom = 2 To Precision
        For Numer = 1 To (Denom - 1)
            Frac = Numer / Denom
            If Frac = MyDecimal Then
                FractDisplay = WholePart & CStr(Numer) & "/" & CStr(Denom)
                GoTo NegCheck
            End If
            If Abs(Frac - MyDecimal) < Abs(ChosenFrac - MyDecimal) Then
                FractDisplay = WholePart & CStr(Numer) & "/" & CStr(Denom)
                ChosenFrac = Frac
            End If
        Next Numer
    Next Denom
 
NegCheck:
    If Neg Then FractDisplay = "-" & FractDisplay
End Function

[Webtest]

I didn't even know that Excel had fractional notation! I've never needed it though. Learn something new every day!

[VB4fun]

DKenny you rock !

Works perfectly, Thanks!