theta
11-28-2012, 05:15 AM
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") )
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") )