Thanks to a helpful post and run by Rawb I have been using the below code.
It works great with only one issue, I am hoping to get help with. If the field I am converting is a whole number it still converts it. (i.e if the answer is 1 this returns 1-1/1)
Is there way to correct this? I would assume there is a way to put at the beginging if Whole number exit function?
It works great with only one issue, I am hoping to get help with. If the field I am converting is a whole number it still converts it. (i.e if the answer is 1 this returns 1-1/1)
Is there way to correct this? I would assume there is a way to put at the beginging if Whole number exit function?
Code:
Public Function Getfraction(Number As Double, Optional denominator As Long = 32, Optional Spacer As String = "-") As Variant
' This function returns a fractional representation of a decimal number.
' Number = The decimal representation of the number you want to convert.
' Required.
' Denominator = The max value of the denominator that will be accepted after
' conversion. Will round the number as necessary to find the nearest
' acceptable value. It ignored, will default to 64ths.
' Spacer = The spacing character to be used between the integral and
' fractional values of the converted number. Typically a space " " or dash
' "-". If ignored, will default to a dash.
Dim nbrGCD As Long
Dim nbrNumerator As Long
Getfraction = -1 ' Initial value!
If Int(Number) = Number Then
Getfraction = CStr(Number)
Else
' Round the number to the closest possible value (within the limitations of
' our specified Denominator)
nbrNumerator = CLng((Number - Int(Number)) * denominator)
' Find the GCD of our (rounded) Numerator and Denominator numbers
nbrGCD = GetGCD(nbrNumerator, denominator)
' Just in case, specify 1 as the lowest possible GCD
If nbrGCD <= 0 Then
nbrGCD = 1
End If
' Use the GCD to reduce our fraction to the "lowest" possible value
nbrNumerator = nbrNumerator / nbrGCD
denominator = denominator / nbrGCD
' Return the resulting fractional number (as a String)
If Int(Number) = 0 Then
Getfraction = CStr(CLng(nbrNumerator)) & "/" & denominator
Else
Getfraction = CStr(Int(Number)) & Spacer & CStr(CLng(nbrNumerator)) & "/" & denominator
End If
End If
End Function
Public Function GetGCD(Number1 As Long, Number2 As Long) As Long
Dim nbrN1 As Long
Dim nbrN2 As Long
Dim nbrTmp As Long
nbrN1 = Number1
nbrN2 = Number2
Do While nbrN1
If nbrN1 < nbrN2 Then
nbrTmp = nbrN1
nbrN1 = nbrN2
nbrN2 = nbrTmp
End If
nbrN1 = nbrN1 Mod nbrN2
Loop
GetGCD = nbrN2
End Function