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 Sub
```

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

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

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.

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.

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

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

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

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

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

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