VBA Function Val Problem

karraj · Mar 29th, 2002, 04:34 PM

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 · Mar 29th, 2002, 04:58 PM

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 · Mar 29th, 2002, 05:02 PM

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

Dave Hawley · Mar 29th, 2002, 05:20 PM

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 · Mar 29th, 2002, 05:46 PM

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

Dave Hawley · Mar 29th, 2002, 05:59 PM

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 · Mar 29th, 2002, 07:25 PM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 29th, 2002, 04:34 PM	#1
karraj Board Regular Join Date: Mar 2002 Posts: 76	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

Mar 29th, 2002, 04:58 PM	#2
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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.

Mar 29th, 2002, 05:02 PM	#3
karraj Board Regular Join Date: Mar 2002 Posts: 76	It happens if there is a % sign at the end ie. "28.4%"

Mar 29th, 2002, 05:20 PM	#4
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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

Mar 29th, 2002, 05:46 PM	#5
karraj Board Regular Join Date: Mar 2002 Posts: 76	any pointers why rounding is done with the %? stripping the text of % returns the number?

Mar 29th, 2002, 05:59 PM	#6
Dave Hawley Banned Join Date: Feb 2002 Posts: 1,582	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.

Mar 29th, 2002, 07:25 PM	#7
daleyman Board Regular Join Date: Mar 2002 Location: London, UK Posts: 167	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