VBA Val Function

Publication Date :

Blog Author :

Download FREE VBA Val Function In Excel Template and Follow Along!
VBA Val Function Template.xlsm

Table Of Contents

arrow

Excel VBA Val Function

The Val function in vba comes under the String functions. It is also a built-in function in VBA that one may use to get the numeric values from a data variable. For example, suppose if A variable has a value as A10, then the VAL function will give us 10 as output. This is because it takes a string as an argument and returns the numbers present in the string.

VAL stands for VALUE in VBA terminology. This function converts the string containing numbers to an actual number. For example, if you supply the text string "1234 Global," it will return only the numerical part, i.e., 1234.

When we download or get the data from web numbers, usually, they are stored as text values in a spreadsheet. However, conversion of text to numbers is the hardest task if you are unaware of the correct function in Excel. As a regular worksheet function, we have a function called VALUE, which will convert all the strings representing numbers to exact numbers with a simple function in the worksheet. This article will show how VBA can achieve this using the VAL function.

VBA Val Function
You are free to use this image on your website, templates, etc.. Please provide us with an attribution link

Syntax

It has only one argument: String.

VBA Val Formula
  • String: It is simply a string value. We are trying to get the numerical part out of it.

So, the VAL function converts the supplied string to a numerical value.

Note: The VAL function always ignores the space characters and continues to read the numbers after the space character or characters.

For example, if the supplied string is "145  45    666 3," it will ignore the space characters and return the result as "145456663."

Examples of VAL Function in Excel VBA

Example #1

Let us try the first example with a simple number, "14 56 47."

The below code is for you.

Code:

Sub Val_Example1()

   Dim k As Variant

   k = Val("14 56 47")
   'Convert the above as 145647

   MsgBox k

End Sub
VBA Val Example 1

When you run VBA code using the F5 key or manually, it will return the result as "145647" by ignoring all the space characters, as shown below.

VBA Val Example 1-1

Example #2

In this example, we will see that the result of the string is "+456."

Code:

Sub Val_Example2()

   Dim k As Variant

   k = Val("+456")
  'Convert the above as 456

   MsgBox k

End Sub
Example 2

You can run this code manually or through the F5 key to return the value as 456 by ignoring +456.

Example 2-1

Example #3

Now, let's try the same number with a negative sign.

Code:

Sub Val_Example3()

   Dim k As Variant

   k = Val("-456")
  'Convert the above as -456

   MsgBox k

End Sub
VBA Val Example 3

This code will only return the value as -456 because it should show the number with the operator sign.

VBA Val Example 3-1

Example #4

Now, let's try this string "100 Kg."

Code:

Sub Val_Example4()

    Dim k As Variant

    k = Val("100 KG")
    'Ignores KG and returns only 100

    MsgBox k

End Sub
Example 4

If you run this code manually or using the F5 key, the above code ignores "KG" and returns only "100" in the VBA message box

VBA Val Example 4-1

Example #5

Now, try the date string, "14-05-2018."

Code:

Sub Val_Example5()

   Dim k As Variant

   k = Val("14-05-2019")
  'Returns 14 as the result.

   MsgBox k

End Sub
VBA Val Example 5

The above code returns 14 because the VAL function can only fetch the numerical value until it finds any other than the numerical character.

VBA Val Example 5-1

Example #6

Now, try the string "7459Good456."

Code:

Sub Val_Example6()

    Dim k As Variant

    k = Val("7459 Good 456")
    'Returns 7459 as the result.

    MsgBox k

End Sub
Example 6

It will extract the numbers until it finds the non-numeric character, i.e., the result is 7459. Then, even though there are numerical values after the non-numerical value "Good," it completely ignores numbers.

VBA Val Example 6-1

Example #7

Now, try the string value "H 12456."

Code:

Sub Val_Example7()

    Dim k As Variant

    k = Val("H 12456")
    'Returns 0 as the result.

    MsgBox k
 
End Sub
Example 7

Run the above code using shortcut key F5 or manually, then it returns the result as zero. Because the first character of the string we supplied is non-numerical, the result is zero.

VBA Val Example 7-1

Example #8

Now try this string “24545    .   2”.

Code:

Sub Val_Example8()

    Dim k As Variant

    k = Val("24545 . 2")
    'Returns 24545.2 as the result.

    MsgBox k

End Sub
Example 8

The code returns the result as 24545.2 because the VBA VAL function considers the character dot (.) as the decimal character and returns the result accordingly.

VBA Val Example 8-1