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.
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
| 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.
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 SubThe code will return the starting number from the string in a Message box.
➤ Press F5.
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 SubThe 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)
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 SubThe 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.
Insert a positive number with a plus sign.
➤ Enter the following code,
Sub VBA_VAL_FUNCTION()DimX 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.
Insert a negative number.
➤ Enter the following code,
Sub VBA_VAL_FUNCTION()
Dim X As Variant
X = Val(" -75")
MsgBox X
End SubThe 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.
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 SubThe 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.
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 Mod Operator (9 Examples)
- How to Use VBA InStrRev Function (7 Suitable Examples)
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 SubThe 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.
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
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 SubThe 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.
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 SubThe 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.
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.
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.
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.
➤ Enter the following code in Module (Code) window,
Sub VBA_VAL_FUNCTION()
Range("C5").Value = Val(Range("C4"))
End SubThe 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.
You will get the return of the VAL function in cell C5.
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
- How to Use VBA WeekdayName Function in Excel (2 Examples)
- Use VBA MkDir Function in Excel (6 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)
