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:

The Val function ignores spaces in the supplied String, but continues to read the characters after any space(s).
If the Val function encounters a character that is not recognised as part of a number, it stops reading the String at this point. Characters that cannot be recognised as parts of numbers include currency symbols, the % symbol and commas.

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:

If the supplied String contains spaces, the Val function ignores these, and continues to read the remaining characters.
If the supplied String starts with a $ symbol, this is not recognised as part of a number, and so the Val function ignores this, and all the remaining characters, and returns the value 0.
If the supplied String contains one or more commas, these are not recognised as part of a number, and so the Val function ignores the first comma and all characters after this.

Return to the VBA Functions Page
Return to the Excel VBA Tutorial Page