Function VBA: Val

Description

The VBA Val function returns the numbers contained in a character string until it finds a non-numeric character.

This function only accepts "." as a decimal separator.

Syntax Val

Example VBA Val

Using the Val function to return strings of various characters as a number:

Sub ValExample()
    
    MsgBox Val("1")           'Returns : 1
    MsgBox Val(" 1 ")         'Returns : 1
    MsgBox Val(1)             'Returns : 1
    
    MsgBox Val("1h")          'Returns : 1
    MsgBox Val("h1")          'Returns : 0

    MsgBox Val("1 number")    'Returns : 1
    MsgBox Val("number 1")    'Returns : 0
    
    MsgBox Val("2 2")         'Returns: 22
    MsgBox Val("2.2")         'Returns: 2.2
    MsgBox Val("2,2")         'Returns : 2
    
    MsgBox Val("75000 Kyiv")  'Returns: 75000
    MsgBox Val("Kyiv 75000")  'Returns : 0
    
    MsgBox Val("Excel")       'Returns : 0
    
End Sub