VBA convert string issue

[thorne_]

Hi all, sorry if you all see the title and groan but im banging my head against a wall here. Whats happening is we have decimals that are formatted in a ??/?? format in excel. This is fine, however certain values will default to undesired ones eg 6/4 goes to 3/2. to combat this the cell automatically changes 3/2 to "6/4".

So in the vba which processes this value we use cdbl("6/4") however this always fails with a type mismatch error.

I have written a function that returns the decimal value (1.5) but i also need to keep the original formatting!!


Any suggestions?

Thanks in advance

[Demosthine]

Good Morning.

I'm not entirely sure what your end goal is or what you mean when you say you got it to return the decimal value but need it to keep the original formatting. If you could provide a Workbook example, we might be able to help better.

But I also came up with a small example that you can use in your VBA. Using this method, you can easily modify the code to work whatever mathematical magic you need it to using the Numerator and Denominator without affecting the original value.

Place the below code in a Module. This will allow you to use the Function through a Cell Function (i.e. A2 is "=DblFromFrac(A1)") or through a Function Call in VBA.

[VBA]

Public Function DblFromFrac(Fraction As String) As Double
' Declare a Variable that will store the results of the
' Split Function
Dim strSplit() As String

' Split the Fraction into two separate parts based on
' the Fraction Sign "/": the numerator and the
' denominator.
strSplit = Split(Fraction, "/", , vbTextCompare)
' If the Split returned anything other than just a
' two-element Array (0) and (1), then there was an
' error in the original Fraction.
If UBound(strSplit) <> 1 Then
' Handle the Error here.
Else
' Define Variables that will store the Numerator
' and the Denominator.
Dim intNumerator As Integer
Dim intDenominator As Integer
' Assign the Numerator and Denominator from our
' Split Variable to the appropriate Fraction
' Variable.
intNumerator = CInt(strSplit(0))
intDenominator = CInt(strSplit(1))

' Return the Double Value calculated from the
' Split Variables.
DblFromFrac = intNumerator / intDenominator
End If
End Function
[/VBA]

I'll wait for more of an explanation or example to hopefully help you better.
Scott

[Bob Phillips]

If you want the decimal to 4ths, then use a format of #/4