VBA Function Val Problem

karraj

Board Regular
Joined
Mar 5, 2002
Messages
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
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
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.
 
Upvote 0
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
 
Upvote 0
any pointers why rounding is done with the %?
stripping the text of % returns the number?
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top