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.

**Table of Contents**hide

## 📂 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

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:** **VBA Format Function in Excel (8 Uses with 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.

**Related Content:** **How to Use VBA Replace Function in Excel (11 Applications)**

**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)****VBA EXP Function in Excel (5 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.

**Related Content: Excel Formula to Generate Random Number (5 examples)**

### 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 Use VBA Val Function in Excel (7 Examples)**

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