VBA Function Val Problem

[karraj]

I am using teh Val function in my vba code and it rounds off the number in the string, for example "28.7" to 29 and "28.3" to 28.
Is there a way to report the numbers in the string as they are!
Thanks for the help.
Karraj

[daleyman]

Have tested this and don't agree that this happens, are you outputting the result to cells where the format does not allow the detail to be shown?

I usually use the newvalue=ABS("28.4") function for this, but guess only due to habit.

[karraj]

It happens if there is a % sign at the end ie. "28.4%"

[Dave Hawley]

Hi Karraj

I am not to sure what you want returned, but this may help

Dim sVal As Single

sVal = CSng(Left("28.4%", Len("28.4%") - 1))
[a1] = sVal / 100
End Sub

[karraj]

any pointers why rounding is done with the %?
stripping the text of % returns the number?

[Dave Hawley]

Sure

For Excel Help:

The Val function stops reading the string at the first character it can't recognize as part of a number. Symbols and characters that are often considered parts of numeric values, such as dollar signs and commas, are not recognized

This also includes decimal points, as you have found out.

[daleyman]

solution of using Left() would work fine so long as the % is always there. else you could use a separate function to strip invalid characters from the string before converting it to a value...


Sub test()
Range("b2").Value = trim_numeric("21.46%")
End Sub

Function trim_numeric(strg As String) As Double
strg2 = "0"
For charpos = 1 To Len(strg)
charx = Mid(strg, charpos, 1)
If (charx >= "0" And charx <= "9") Or charx = "." Then
strg2 = strg2 & charx
End If
Next
trim_numeric = Abs(strg2)
End Function