Excel VBA Val Function

VBA Val Function: The Val function in VBA converts the given string into a numeric value. This function ignores spaces and continues to read the characters after space(s). It stops reading the String if the numbers include currency symbols, the % symbol and commas.

Syntax

Val (String)

Parameter

String (required) – This parameter represents the string value that you want to convert to numeric.

Return

This function returns a numeric value after converting the specified string into numeric.

Example 1

Sub ValFunction_Example1()
 ' Converting the strings into numeric values.
 Dim num_val As Double
 num_val = Val("500")
 ' The variable num_val will return 500.
 Cells(1, 1).Value = num_val
 End Sub

Output

500

Example 2

Sub ValFunction_Example2()
 ' Converting the strings into numeric values.
 Dim num_val As Double
 'will ignore the text after the spaces
 num_val = Val("11.5  cm  ")
 ' The variable num_val will return 11.5.
 Cells(1, 1).Value = num_val
 End Sub

Output

11.5

VBA Val Function

Example 3

Sub ValFunction_Example3()
 ' Converting the strings into numeric values.
 Dim num_val As Double
 'will ignore the spaces
 num_val = Val("11.5  11.5  ")
 ' The variable num_val will return 11.511.
 Cells(1, 1).Value = num_val
 End Sub

Output

11.511

VBA Val Function

Example 4

Sub ValFunction_Example4()
 ' Converting the strings into numeric values.
 Dim num_val As Double
 'all characters after the comma are ignored
 num_val = Val("15,001")
 ' The variable num_val will return 15.
 Cells(1, 1).Value = num_val
 End Sub

Output

15

VBA Val Function

Related Topics

Excel VBA AutoFilter

One of the reasons for Excel VBA’s popularity is its capability to filter and analyze data from huge database with the help of a method known as AutoFilter. This method permits a...

4 minutes read.

Excel VBA InStr Function

VBA InStr Function The InStr function in VBA searches for a substring inside the given string. It returns the position of a substring within a string, as an integer if the substring is found...

1 minute read.

Excel VBA Trim Function

The Trim function in VBA removes the leading and trailing spaces from the specified string. Syntax Trim (String) Parameter String (required) – This parameter represents the string from which you want to remove the leading and...

1 minute read.

Excel VBA DatePart Function

The DatePart function in VBA returns a part (day, month, week, etc.) for the specified date and/or time. Syntax DatePart (Interval, Date, [FirstDayOfWeek], [FirstWeekOfYear]) Parameter Interval (required) – This parameter represents a string specifying the interval to be used. It can...

2 minutes read.

Excel VBA Day Function

The function Day in VBA returns the day number (from 1 to 31) for the given date value. Syntax Day (Date) Parameter Date (required) – This parameter represents the date. Return This function returns the day...

1 minute read.

VBA Regex Pattern Validation

VBA Regex Pattern Validation The key purpose of using Regex in VBA was to validate the data and extract the same category of data. There are certain formats that are standard...

6 minutes read.

Excel VBA FormatNumber Function

VBA FormatNumber Function: The FormatNumber function in VBA is used to apply a number format to a numeric expression, and it returns the result as a string. Syntax FormatNumber (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits]) Parameter Expression (required)...

2 minutes read.

Excel VBA For Each Loop

A For Each loop executes a statement or a group of statements for each element in an array or collection. It repeats the statement/condition/code for each element in a collection. For Each Loops loop through every...

4 minutes read.

Four VBA Clear methods

Four VBA Clear methods In Microsoft Excel, many times, a situation arises where the user wants to clear the data or any specific range of data. What if the user automates this task with...

6 minutes read.

Excel VBA CDec Function

VBA CDec Function: The CDec function in VBA converts an expression into a Decimal data type. Syntax CDec (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

VBA Updating Pivot Table

VBA- Updating Pivot Table The pivot table is an important feature to explore, summarize, and interpret the bulk amount of data. It helps data evaluating, reviewing, as well as making useful...

3 minutes read.

Excel VBA Fix Function

VBA Fix Function: The Fix function in VBA truncates the given number to an integer and returns the rounded off integer number. This function both positive and negative numbers to zero. Syntax Fix...

1 minute read.

If ElseIf ElseIf Statement or Nested If statement in VBA

VBA Excel: If … ElseIf … ElseIf Statement or Nested If statement This function enables you to check multiple conditions and, based on that, then run one of the statement blocks present. If...

2 minutes read.

Excel VBA Format Function

VBA Format Function The format function in VBA applies a specified format to an expression and returns the result as a string. Syntax Format (Expression, [Format], [FirstDayOfWeek] , [FirstWeekOfYear] ) Parameter Expression (required)- This parameter represents the expression that you want to format. Format...

3 minutes read.

Excel VBA ABS Function

VBA ABS Function: The ABS function in VBA returns the absolute value of the specified number. Syntax Abs (Number) Parameter Number (required) – This parameter represents the number that you want the absolute value of. Return This...

1 minute read.

Excel VBA IsMissing Function

VBA IsMissing Function: The IsMissing function in VBA checks if any parameter to a procedure is missing or not. It returns a Boolean value True if the specified parameter has not been...

1 minute read.

Excel VBA Sin Function

VBA Sin Function: The Sin function in VBA returns the sine value for a supplied angle. Syntax Sin (Number) Parameter Number (required) –This parameter represents the angle (in radians) to calculate the sine value. Return This function...

1 minute read.

Steps to Create a Pivot Table

Steps to Create a Pivot Table: Pivot Tables can be easily automated through VBA coding. To create a Pivot Table in VBA, follow the below step by step procedure to...

4 minutes read.

Excel VBA While wend Loop

WHILE wend loop is used when the user is not sure how many times they want to execute the VBA code within the program. With a WHILE loop, the loop body may...

3 minutes read.

Excel VBA CVErr Function

VBA CVErr Function: The CVErr function in VBA returns an Error data type, involving with a user-specified error code. Syntax CVErr (Expression) Parameter Expression (required)- This parameter represents the required error code. Return This function returns an...

1 minute read.