How to Use VBA Val Function in Excel (7 Examples)

The VBA Val function is an Excel Text function which converts a text string into a numeric value. You can use this function in a VBA code to get the starting number part of a string.

The above image gives a general overview of the VAL function. From the article, you will get to know the details of the VBA VAL function and how you can apply the function in different conditions.

📂 Download Practice Workbook

Introduction to the VBA Val Function

❑ Objective

The VAL function returns the number contained in the beginning of a text string as a numeric value.

❑ Syntax

Val (String as String)

❑ Argument Explanation

❑ Output

The VAL function returns numeric value.

❑ Version

This function is available from Excel 2000. So you can use this function in any version newer than Excel 2000.

7 Examples of Using VBA Val Function in Excel

Now, Let’s see different Examples of using the VBA VAL function in various conditions.

1. Getting the Numerical Value from a String with Number and Text

You can get the numeric value from the front end of a string which contains both number and text. Suppose you have a string “308 Park Street”. Now, to get the number part from this string,

➤ Press ALT+F11 to open the VBA window.

In the VBA window,

➤Go to the Insert tab and select Module.

It will open the Module(Code) window.

➤ Type the following code in the Module(Code) window,

Sub VBA_VAL_FUNCTION()

    Dim X As Variant
    X = Val(" 308 Park Street ")
    MsgBox X

End Sub

The code will return the starting number from the string in a Message box.

➤ Press F5.

As a result, the starting number of the input string will be returned in a message box.

The VAL function stops reading a string when it faces any string that it can’t recognize as a number. As a result, the function only returns the number which appears before any other kind of string such as alphabet, symbol or any other non-numeric characters. Suppose you have inserted the string “ 308 Park Street 2013 ” in your code,

Sub VBA_VAL_FUNCTION()

      Dim X As Variant
      X = Val(" 308 Park Street ")
      MsgBox X

End Sub

Now, if you look at the output you will see the message box is only displaying the number 308. The code stopped reading when it faced the string P. So, it has returned only the beginning number (308) and avoided the ending number (2013).

Read More: How to Use VBA IsNumeric Function (9 Examples)

2. VBA Val with Positive and Negative Number

Now, let’s see how the VBA VAL function deals with positive and negative number.

First, let’s insert a simple positive number.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

       Dim X As Variant
       X = Val(" 75 ")
       MsgBox X

End Sub

Here the input string is a plain positive number.

➤ Press F5.

As a result, the output will be shown in a message box.

Now, if you look at the output you will see exactly the same number of the input has been returned.

Now, let’s insert a positive number with a plus sign.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

   Dim X As Variant
   X = Val(" +75")
   MsgBox X
End Sub

Here the input string is a positive number with a plus sign.

➤ Press F5.

As a result, the output will be shown in a message box.

Now, if you look at the output you will see that this time the plus sign has been omitted and only the number has been returned.

Now let’s insert a negative number.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

     Dim X As Variant
     X = Val(" -75")
     MsgBox X

End Sub

Here the input string is a negative number.

➤ Press F5.

As a result, the output will be shown in a message box.

Now, if you look at the output you will see that this time both the negative sign and the number have been returned in the output.

Read More: Excel Formula to Generate Random Number (5 examples)

3. Text String Starting with an Alphabet

The VAL function stops reading a string when it faces any string which it can’t recognize as a number. So, the function returns 0 in the output when you give a string starting with an alphabet.

To check this,

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

   Dim X As Variant
   X = Val(" Service 49")
   MsgBox X

End Sub

Here the input string starts with an alphabet.

➤ Press F5.

As a result, the output will be shown in a message box.

Now, if you look at the output you will see that it is showing 0. This is because the input was started with an alphabet, not with a number.

Read More: How to Use VBA Str Function in Excel (4 Examples)

Similar Readings

• How to Use the VBA Environ Function (4 Examples)
• Use VBA Int Function in Excel ( 3 Examples)
• How to Use VBA IsEmpty Function (5 Relevant Examples)
• How to Use VBA Mod Operator (9 Examples)
• How to Use VBA InStrRev Function (7 Suitable Examples)

4. VBA Val Function for Number with Special Symbols

The VBA VAL function can’t recognize special symbols (such as &,@, %, etc.) and separators [such as comma (,), hyphen(–), underscore(_)]. So, it returns the part of the number which appears before the separators. Let’s say you have the number 250,890.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

    Dim X As Variant
    X = Val(" 250,890")
    MsgBox X

End Sub

Here the input is a number using a comma as a separator.

➤ Press F5.

As a result, the output will be shown in a message box.

You will see the output has returned 250 which is the part of the number before the comma.

Similarly, If you use a hyphen as the separator, you will only get the digit which appears before the hyphen in the returned number.

Sub VBA_VAL_FUNCTION()

   Dim X As Variant
   X = Val(" 250,890")
   MsgBox X

End Sub

The VAL function doesn’t count space as a string. So, if your number has space in between the digit the function will omit the space and will return the full number.

Sub VBA_VAL_FUNCTION()

   Dim X As Variant
   X = Val(" 250 890 ")
   MsgBox X

End Sub

Excel VBA VAL function counts dot (.) as a part of the number. So, if you give a number with decimal points, the function won’t omit the dot and will return the full number.

Sub VBA_VAL_FUNCTION()

   Dim X As Variant
   X = Val(" 1205.48 ")
   MsgBox X

End Sub

Now, let’s give a string with a number with decimal points and texts in the argument of the VAL function.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

    Dim X As Variant
    X = Val(" 1205.48 meters ")
    MsgBox X

End Sub

Here the input is a string with a number with decimal points and texts.

➤ Press F5.

As a result, the output will be shown in a message box.

You will see the output has returned 1205.48. That means the function has returned the number part from the string and omitted the text part.

Read More: How to Use the VBA Chr Function (2 Examples)

5. Getting the Month from a Date Using Val Function

You can get the month of a date using the VBA VAL function. In this example, I’ll use the VBA Dateserial function to insert the date and then the VAL function to get the month of the inserted data.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

    Dim X As Variant
    X = Val(VBA.DateSerial(2021,8,17))
    MsgBox X

End Sub

Here the input of the VAL function is the VBA DateSerial function which will give a date as the argument of the VAL function

➤ Press F5.

As a result, the month of the date will be shown in the output message box.

Read More: VBA Date Function (12 Uses of Macros with Examples)

6. Decimal Value of Octal or Hexadecimal Number

You can use the VAL function to convert an Octal or Hexadecimal number into a decimal number.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

   Dim X As Variant
   X = Val(&O1453)
   MsgBox X

End Sub

Here &O.indicates the input is an octal number.

➤ Press F5.

The function will convert the Octal number 1453 into its decimal value 811.

Now, let’s convert a hexadecimal number into a decimal number.

➤ Type the following code,

Sub VBA_VAL_FUNCTION()

  Dim X As Variant
   X = Val(&H10B2)
   MsgBox X

End Sub

Here &H.indicates the input is a hexadecimal number. B in the argument string will be counted as a digit of a hexadecimal number, not as a text string.

➤ Press F5.

As a result, the function will convert the hexadecimal number 10B2 into its decimal value 4274.

7. VBA Val Function Output in Worksheet

In this example, I’ll show you how you can insert the argument and get the result in your worksheet.

Suppose, you have the text string 123 Park Avenue 38 in cell C4. Now, you want to use this text as the argument of the VBA VAL function and get the result in cell C5.

To do that,

➤ Type the following code in Module (Code) window,

Sub VBA_VAL_FUNCTION()

   Range("C5").Value = Val(Range("C4"))

End Sub

The code will use the text of cell C4 as the argument of the VAL function and will give the return of the VAL function in cell C5.

➤ Press F5 and close the VBA window.

Now, you will get the return of the VAL function in cell C5.

Read More: How to Create Custom VBA functions and Use them in the Worksheet

💡 Things to Remember

📌 Only the period (.) is recognized by the VAL function as a valid decimal separator. Use CDbl to convert a string to a number when different decimal separators are needed.

📌 The VAL function will stop reading the string if it detects a character that isn’t recognized as part of a number. Currency symbols ($), the percent symbol (%), and commas (,) are examples of characters that cannot be recognized as numbers.

Conclusion

I hope now you know what the VBA VAL function is and how you can use this function in Excel. If you have any confusion please feel free to leave a comment.

Related Articles

• How to Use VBA WeekdayName Function in Excel (2 Examples)
• Use VBA MkDir Function in Excel (6 Examples)
• How to Use VBA TimeSerial in Excel (3 Examples)
• Use VBA Round Function in Excel (6 Quick Uses)
• How to Use Concatenate in Excel VBA (4 Methods)
• How to Use VBA Function Procedure with Arguments in Excel
• VBA EXP Function in Excel (5 Examples)