The VBA Val Function

Related Function:
VBA Str

Description

The VBA Val function converts a supplied string into a numeric value.

The syntax of the function is:

Val( String )

Where the String argument is the string that you want to convert into a number.

Note:


VBA Val Function Examples

Example 1

' Convert four strings into numeric values.
Dim num1 As Double
Dim num2 As Double
Dim num3 As Double
Dim num4 As Double
num1 = Val( "500" )
' num1 is now equal to 500.
num2 = Val( "+10.9" )
' num2 is now equal to 10.9.
num3 = Val( "-0.5" )
' num3 is now equal to -0.5.
num4 = Val( "10.5 km" )
' num4 is now equal to the String "10.5".

Note that, in the above example, when the Val function is supplied with the string "10.5 km", which contains both numbers and text, the function ignores everything after the first character that is not recognised as part of a number and simply returns the number 10.5.


Example 2 - Possible Unexpected Results

As the Val function ignores spaces and also ignores everything after the first character that is not recognised as part of a number, you may sometimes get results that you do not expect. Some examples are shown below.

' Convert two strings into numeric values.
Dim num1 As Double
Dim num2 As Double
Dim num3 As Double
num1 = Val( "10     10" )
' num1 is now equal to 1010 (spaces are ignored).
num2 = Val( "$500.00" )
' num2 is now equal to 0 (all characters after the $ symbol are ignored).
num3 = Val( "1,000" )
' num3 is now equal to 1 (all characters after the comma are ignored).

Note that in the above examples: