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.

vba val

The above image gives a general overview of the VAL function.


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

❑ Argument Explanation

Argument Required/Optional Explanation
String Required Any Valid Sting Expression

❑ 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

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

Suppose you have a string “308 Park Street”. 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.

module

It will open the Module(Code) window.

➤ Enter 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.

vba val

➤ Press F5.

The starting number of the input string will be returned in a message box.

vba val

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

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).

vba val

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


Example 2 – VBA Val with Positive and Negative Number

Insert a simple positive number.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

The input string is a plain positive number.

➤ Press F5.

The output will be shown in a message box.

If you look at the output, exactly the same number of the input has been returned.

vba val

Insert a positive number with a plus sign.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

The input string is a positive number with a plus sign.

➤ Press F5.

The output will be shown in a message box.

If you look at the output, the plus sign has been omitted and only the number has been returned.

vba val

Insert a negative number.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

The input string is a negative number.

➤ Press F5.

The output will be shown in a message box.

If you look at the output, both the negative sign and the number have been returned in the output.

vba val

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


Example 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.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

The input string starts with an alphabet.

➤ Press F5.

The output will be shown in a message box.

If you look at the output, it is showing 0. This is because the input was started with an alphabet, not with a number.

vba val

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


Similar Readings


Example 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.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

The input is a number using a comma as a separator.

➤ Press F5.

The output will be shown in a message box.

The output has returned 250 which is the part of the number before the comma.

vba val

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

vba val

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

vba val

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

vba val

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

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

The input is a string with a number with decimal points and texts.

➤ Press F5.

The output will be shown in a message box.

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

output

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


Example 5 – Getting the Month from a Date Using Val Function

You can get the month of a date using the VBA VAL function.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

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.

The month of the date will be shown in the output message box.

output

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


Example 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.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

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

➤ Press F5.

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

output

Convert a hexadecimal number into a decimal number.

➤ Enter the following code,

Sub VBA_VAL_FUNCTION()

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

End Sub

&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.

The function will convert the hexadecimal number 10B2 into its decimal value 4274.

output


Example 7 – VBA Val Function Output in Worksheet

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

data

➤ Enter 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.

output

➤ Press F5 and close the VBA window.

You will get the return of the VAL function in cell C5.

result


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.


Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Prantick Bala
Prantick Bala

PRANTICK BALA is a marine engineer who loves exploring Excel and VBA programming. Programming, to him, is a time-saving tool for efficiently managing data, files, and internet tasks. He is proficient in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, Excel, and VBA and goes beyond the basics. Armed with a B.Sc in Naval Architecture & Marine Engineering from BUET, he has transitioned into the role of a content developer. Beyond his work, find him immersed in books, exploring new... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo