# VBA Function Val Problem

This is a discussion on VBA Function Val Problem within the Excel Questions forums, part of the Question Forums category; I am using teh Val function in my vba code and it rounds off the number in the string, for ...

1. 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

2. 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.

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

4. 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

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

6. 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.

7. 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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•