How can I improve this - return fractions

[theta]

Hi...I built 2 functions that work together. But I can see that the double IF looks a bit messy. How could I tidy these up or make it more elegant / robust??

This assumes the user will input values into a cell. They may enter it as a fraction or as a decimal - I would like to get a fraction in both instances. The user is limited to :

=1/1
=1/2
=1/4
=1/12

1
0.5
0.25
0.08333 (irrational)

The function also allows for an optional specification of the fraction (/12, /6, /2)


'Retrieve the text of a formula - =1/12 > Evaluate(1/12)
Public Function FormulaText(rng As Range)
If rng.HasFormula Then
FormulaText = Replace(rng.Formula, "=", "", 1, 1)
Else
FormulaText = rng.Value
End If
End Function
'Retrieve the fraction of a decimal
Public Function DecimalFraction(val As Variant, Optional fixed As Integer)
'Check is already expressed as a fraction
If InStr(1, val, "/") <> 0 Then
If fixed = 0 Then
DecimalFraction = val
Else
DecimalFraction = Excel.WorksheetFunction.Text(Evaluate(val), "# ?/" & fixed)
End If
Else
If fixed = 0 Then
DecimalFraction = Excel.WorksheetFunction.Text(val, "# ?/??")
Else
DecimalFraction = Excel.WorksheetFunction.Text(val, "# ?/" & fixed)
End If
End If
End Function


I use it in the format DecimalFraction ( FormulaText (sht.Range("InputValue") )

[snb]

I don't think you need the function: formulatext

I'd use:

Function DecimalFraction(val As Variant, Optional fixed As Integer)
DecimalFraction = ""
If InStr(val, Application.DecimalSeparator) Then DecimalFraction = Left(Split(val, Application.DecimalSeparator)(1), IIf(fixed = 0, Len(val), fixed))
End Function

[theta]

I don't think you need the function: formulatext

I'd use:

Function DecimalFraction(val As Variant, Optional fixed As Integer)
DecimalFraction = ""
If InStr(val, Application.DecimalSeparator) Then DecimalFraction = Left(Split(val, Application.DecimalSeparator)(1), IIf(fixed = 0, Len(val), fixed))
End Function

I am not sure what your function does...the results are not fractions, or similar to the original function?

I would like to be able to feed it a range containing a formula =1/12 or a value 0.08333 and have it return a fraction.

Obviously if it already contains a formula fraction then this should be preserved (to prevent 2/12 being expressed as 1/6) e.g.

=1/12 > FormulaText > 1/12

This is then deteched using "/" so is used as the result. If fixed were present then the IF statement applies a different logic and converts it using TEXT to an excel approx fraction (or zero if incorrect).

If formula text does not contain "/" then we can analyse the value and then get excel to pick a fraction for us - default e.g. 0.16666 would be picked as "1/6" but I would like it expressed as a 1/12 so fixed is specified as 12

The logic works - but I just know it could be made more elegant...

[mikerickson]

Perhaps something like
Dim Twelfths As String
Dim Fraction As String
Dim number As Double
number = 0.1305

Twelfths = Evaluate("TEXT(" & number & ", ""? / 12"")")
Fraction = Evaluate("TEXT(" & Twelfths & ", ""? / ??"")")
MsgBox number & vbCr & Twelfths & vbCr & Fraction