VBA Val

VBA Val Function

VBA Val stands for Value. It converts the arrays or string which has some numbers into pure numerical values. Suppose if we give “111 One” as input then we will get only “111” as numerical output. This is quite useful while working in a data which we extract from some kind of database. In that database file, we may encounter such cells which may contain numbers along with extra spaces, hidden characters, special characters or alphabets. In that case, using Val can convert that string into numbers. Which can be used in further analysis.

How to Use Excel Val Function in VBA?

Let’s see the examples of Val in Excel VBA.

Example #1 – VBA Val

It is quite easy to implement. For multiple types of applications and codes, we will first form a frame of code which we will be using multiple times in further examples.

Step 1: Go to Insert menu tab and select a Module as shown below.

Step 2: After that, we will get the blank window of Module. In that, write the sub category of VBA Val in excel or you can use any other name of subcategory as per your need.

Code:

Sub VBA_Val()

End Sub

Step 3: Now define a variable DIM A or choose anything in place of alphabet A as assign it as Variant. Variant allows us to use any kind of data type while assigning the values in the variable.

Code:

Sub VBA_Val()

Dim A As Variant

End Sub

Step 4: And now assign any type of number sequence to variable A under VBA function VAL. We have assigned a combination of sequential number for demonstration as shown below with spaces between them.

Code:

Sub VBA_Val()

Dim A As Variant
A = Val("11 22 33")

End Sub

Step 5: At last we will need a message box to print the values stored in variable A.

Code:

Sub VBA_Val()

Dim A As Variant
A = Val("11 22 33")
MsgBox A

End Sub

Step 6: Now compile the code step-by-step by pressing functional key F5. And then run it by clicking on the play button located below the menu bar as shown below. We will see this it returned the values stored in the variable. An as 112233 as shown below. All these values are without spaces.

Example #2 – VBA Val

In this example, we will see how VBA Val function is used for number containing some mathematical signs. For this, we will consider the code written above. We have taken out the frame of the code which will be used all the examples, as shown below.

Step 1: Go to Insert menu tab and select a Module as shown below

Step 2: As highlighted in the below screenshot, we will keep updating the value between brackets of VAL Function.

Code:

Sub VBA_Val2()

Dim A As Variant
A = Val("")
MsgBox A

End Sub

Step 3: Now let’s insert any number with mathematical sign plus (“+”) as shown below.

Code:

Sub VBA_Val2()

Dim A As Variant
A = Val("+111")
MsgBox A

End Sub

Step 4: Now compile and run the code. We will see, VBA Val has given the values as 111 without the plus sign. It is because logically all the values with or without plus signs are always positive in nature.

Step 5: Let’s change the value in Val function from +111 to -111. Now we will see if minus sign gets converted into the value or not.

Code:

Sub VBA_Val2()

Dim A As Variant
A = Val("-111")
MsgBox A

End Sub

Step 6: Compile the code and run. We will see, the minus sign is still retained in the value and message box has returned the value as -111. Which means any sign other than plus will not get converted with Val function in VBA.

Example #3 – VBA Val

In this example, we will see, how Val function would work for time formats.

Step 1: For this again we will use the above-defined format for Excel VBA Val as shown below.

Code:

Sub VBA_Val3()

Dim A As Variant
A = Val("")
MsgBox A

End Sub

Step 2: Now insert any time format in VAL function as circled in the above screenshot. Here we are adding 11 AM as shown below.

Code:

Sub VBA_Val3()

Dim A As Variant
A = Val("11 AM")
MsgBox A

End Sub

Step 3: Now compile the code and run. We will see, VAL function has eliminated AM from 11 AM and given us only 11 as output as shown below.

Step 4: Now let’s use some different format. Use any minutes with hours. We have used value 11:05 under Val brackets.

Code:

Sub VBA_Val3()

Dim A As Variant
A = Val("11:05 AM")
MsgBox A

End Sub

Step 5: Again compile and run the code. Again Val function has removed colon and minutes numbers along with AM and given us the whole number 11 as shown below.

Example #4 – VBA Val

In this example, we will see how the date format works in this.

Step 1: Again set the format for VBA Val function along with the complete code which we have seen in the above examples.

Code:

Sub VBA_Val4()

Dim A As Variant
A = Val("")
MsgBox A

End Sub

Step 2: Now insert any date format as per your need. We can insert data in a hyphen (“-“) format in a slash (“ / “) format. Let’s use the slash date format which is most often used.

Code:

Sub VBA_Val4()

Dim A As Variant
A = Val("06/26/2019")
MsgBox A

End Sub

Step 3: Now compile the code and run it. We will see VBA Val has returned the numerical values as “6”. Values after slash are not accepted by VBA Val.

Example #5 – VBA Val

In this example, we will see how this will work when the numbers are after the text.

Step 1: Take the format which we have seen above.

Code:

Sub VBA_Val2()

Dim A As Variant
A = Val("")
MsgBox A

End Sub

Step 2: In Val function brackets, let’s put some text and numbers. Let’s consider “AB 11” as shown below.

Code:

Sub VBA_Val2()

Dim A As Variant
A = Val("AB 11")
MsgBox A

End Sub

Step 3: Now run it. We will see, in the message box, only 0 is appearing. Which means VBA Val doesn’t consider the numbers after characters or text.

Pros of VBA Val

• It can be used in the data which is extracted from some kind of tool or database. Which consists of different kind of characters along with numbers.
• It is quite easy to separate numbers by using VBA Val in any kind data.
• We can choose any format which consists of a number or set of number to separate it from other characters.

Things to Remember

• It also considers the decimals.
• Save the file as Marco enable excel so that written would be retained.
• If record this process in VBA, then obtained code will be much lengthier than the examples which we have seen above.
• There is not an alternate insert function available in Excel which gives the same result as VBA Val.

Recommended Articles

This is a guide to VBA Val. Here we discuss how to get Val in VBA Excel along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. VBA InStr explained with Examples
2. VBA Integer Data Type
3. How to Select Cell using VBA Code?
4. Transpose a Range in VBA