VBA Val Function

What is Excel VBA Val Function?

The VBA Val function is a built-in function that converts a string representing a numeric value into an actual numeric data type. It strips any leading and trailing spaces from the input string and converts it into a numeric value. This function is beneficial when dealing with data input or manipulation tasks in Excel VBA, where you need to ensure that text values are treated as numbers.

Let us look at an example. In this example, a string “1,234.56” contains a thousands separator (comma). The Val function doesn’t handle separators, so we use the Replace excel function to remove the comma before applying Val.

When executed, it displays a message box with the converted numeric value, “Converted Value: 1234.56,” illustrating the successful handling of thousands separators.

Syntax

The syntax of the Val function in Excel VBA is as follows:

Val(string)

• string: This is the input string that you want to convert into a numeric value.

How to Use Excel VBA Val Function?

Using the VBA Val function in Excel is straightforward. Follow these steps to use it:

Step 1: In the Excel workbook, press ALT + F11 to open the Visual Basic for Applications (VBA) editor.

Step 2: Inside the VBA editor, go to Insert > Module to insert a new module where you can write your VBA code.

Step 3: Declare any VBA variables you need for your VBA code. In the case of Val, you might declare a variable to hold the input string and another for the result.

Step 4: Set the value of your string variable to the string you want to convert using the VBA Val Function.

Step 5: Use the VBA Val function by calling it with the string variable as its argument. The VBA Val function will convert the string into a numeric value.

Step 6: Depending on your specific needs, you can now use the numeric result for further calculations, display it in a message box, or perform other actions required by your VBA code.

Step 7: Test your VBA code by running it within the VBA Editor. You can do this by clicking the “Run” button or pressing F5.

Examples

Example #1 – Basic Conversion

In this example, we convert the string “123.45” to a numeric value using VBA Val Function and display it in a message box.

Step 1: In the new module, start by creating a new subroutine named “Example1.”

Step 2: Here, we declare a variable named “strNum” as a string data type, which will hold the input string we want to convert to a numeric value.

Step 3: We declare another variable named “result” as a Double data type to store the numeric result after using the VBA Val function.

Step 4: We assign the string value “123.45” to the “strNum” variable in this line. It is the string we want to convert to a number.

Step 5: Here, we use the VBA Val function to convert the string stored in “strNum” into a numeric value. The result is then assigned to the “result” variable.

Step 6: Finally, we display a message box that shows the converted numeric value. We concatenate the text “Converted Value: ” with the “result” variable to display it.

Step 7: Now, save the VBA macro and click on Run. Once you execute it, a message box with “Converted Value: 123.45” will be displayed.

It confirms that the VBA Val function successfully converted the string “123.45” into the numeric value 123.45.

Here is the full code:

Sub Example1()

    Dim strNum As String

    Dim result As Double

        strNum = “123.45”

    result = Val(strNum)

        MsgBox “Converted Value: ” & result

End Sub

Example #2 – Handling Leading and Trailing Spaces

In this example, we have a string with leading and trailing spaces. We will use the VBA Val Function to trim these spaces before conversion.

Step 1: In the new module, start by creating a new subroutine named “Example2.”

Step 2: Here, we declare the “strNum” variable a String data type to store the input string.

Step 3: The “result” variable is declared as a Double data type to store the converted numeric value.

Step 4: We assign the string value ” 456.78 ” to the “strNum” variable in this line. Notice the leading and trailing spaces in the string.

Step 5: We use the VBA Val function to convert the string stored in “strNum” into a numeric value. Val automatically trims the leading and trailing spaces.

Step 6: Finally, we display a message box with the converted numeric value. The message box will show “Converted Value: 456.78,” indicating that Val handled the spaces correctly.

Step 7: Now, save the macro and execute this code. It will display a MsgBox in VBA with “Converted Value: 456.78.”

This message confirms that the VBA Val function successfully converted the string ” 456.78 ” into the numeric value 456.78 while automatically handling leading and trailing spaces.

Here is the full code:

Sub Example2()

    Dim strNum As String

    Dim result As Double

        strNum = ”  456.78  “

    result = Val(strNum)

        MsgBox “Converted Value: ” & result

End Sub

Example #3 – Error Handling

This example demonstrates error handling. If the VBA Val Function cannot convert the string to a number, it returns a message box with the error message.

Step 1: In the new module, we begin with the declaration of a new VBA subroutine named “Example3.”

Step 2: Now, the “strNum” variable is declared as a string data type to store the input string.

Step 3: Next, “result” is declared as a Double data type to store the converted numeric value.

Step 4: We assign the string value “ABC” to the “strNum” variable. This input is not a valid number.

Step 5: Here, we enable error handling using the “On Error Resume Next” statement. This allows us to continue executing code even if an error occurs.

Step 6: We use the VBA Val function to attempt the conversion of the “strNum” string to a numeric value. Since “strNum” contains non-numeric characters, Val will return 0.

Step 7: This line turns off error handling, reverting to normal error behavior.

Step 8: We check if the “result” variable is equal to 0. If it is, it indicates that the conversion failed.

Step 9: If the result is 0, we display a message box indicating that the conversion failed due to the input not being a valid number. If the result is not 0, we proceed to the “Else” block.

Step 10: In the “Else” block, we display a message box with the converted numeric value. This part of the code will not be executed in this example because the conversion failed.

Step 11: Save the macro and click on Run. When you execute it, the code will display a message box with the message “Conversion failed. The input is not a valid number.”

This message appears because the string “ABC” cannot be converted to a valid numeric value using the VBA Val function, and the code includes error handling to gracefully address this situation.

Here is the full code:

Sub Example3()

    Dim strNum As String

    Dim result As Double

       strNum = “ABC”

        On Error Resume Next

    result = Val(strNum)

    On Error GoTo 0

        If result = 0 Then

        MsgBox “Conversion failed. The input is not a valid number.”

    Else

        MsgBox “Converted Value: ” & result

    End If

End Sub

Important Things To Note

1. VBA Val Function removes leading and trailing spaces from the input string but considers any spaces within the string as non-numeric characters.
2. VBA Val Function decimal recognizes decimal points, allowing it to convert floating-point numbers correctly.
3. Implementing error handling when using VBA Val Function is essential, as it may return 0 for invalid conversions.
4. There are alternatives to VBA Val Function, such as using IsNumeric to check if a string can be converted to a number before using Val.

Frequently Asked Questions (FAQs)

Recommended Articles

This has been a guide to VBA Val Function. Here we explain the how to use Excel VBA Val Function and syntax along with examples & downloadable excel template. You can learn more from the following articles –

• VBA Functions
• VBA Wait Function
• VBA DIR Function