VBA CVErr Function

VBA (Visual Basic for Applications) CVErr function is used to return an error number of type Variant that corresponds to a specific error type or null value. This function is especially useful when working with error handling in VBA code.

VBA CVErr Function – Purpose, Syntax and Arguments

Purpose:

The main purpose of the CVErr function is to provide a way for VBA code to handle or ignore specific error types, and to provide more control and information about error handling.

Syntax:

CVErr(#Error_Code#)

Arguments:

• Error_Code: Required. An integer or long value representing the error number or error type to be returned by the function. It can also be a string expression that contains a valid error number.

Example:

Code:

Sub CheckValue()
Dim number As Integer
Dim result As Variant
number = 10
If number > 5 Then
'Divide by Zero error
result = CVErr(200)
MsgBox "Value is greater than 5. Error number: " & result
Else
'Type mismatch error
result = CVErr(13)
MsgBox "Value is less than or equal to 5. Error number: " & result
End If
End Sub

Output:

Value is greater than 5. Error number: #DIV/0!

Remarks:

• The CVErr function can only be used in error handling routines, such as VBA’s ‘On Error’ statement and ‘Resume’ statement.
• The function can return any error number between 200 to 299, ranging from ‘#DIV/0!’ to ‘#NUM!’. These correspond to the most common error types in VBA.
• Using the CVErr function is considered to be a more efficient way of handling errors compared to using the ‘Err’ function, as it returns a variant instead of an error number which can be converted to a string.

Important Notes:

• The CVErr function returns a special value of type ‘Error’, which can be used in VBA code to check for specific error types or ignore specific errors.
• The use of CVErr function is not recommended for beginners or those who are not familiar with error handling in VBA, as it requires a good understanding of error types and error handling methods.

VBA CVErr function is a powerful tool in error handling, providing more control and information about error types in VBA code. It is recommended to have a good understanding of error handling routines in order to use this function effectively.

Understanding VBA CVErr Function with Examples

Converting Error Values to String

Description: The CVErr function in VBA is used to convert an error value into a string format. This can be helpful when handling error messages and displaying them for a user to understand.

Dim errorValue As Variant
errorValue = CVErr(xlErrDiv0)
MsgBox "Error value: " & CStr(errorValue)

• Firstly, we declare a variable ‘errorValue’ as a Variant. This means it can hold any value, including error values.
• Next, we use the CVErr function to assign an error value to our variable. In this example, we use the ‘xlErrDiv0’ error, which is the error for dividing by zero.
• We then use the CStr function to convert our error value to a string, which is necessary to display the error message in a message box.
• Lastly, we use the MsgBox function to display the error value in a message box. The & symbol is used to concatenate the string “Error value: ” with our converted error value.

Explanation:

When working with large amounts of data and calculations, encountering errors is inevitable. The CVErr function allows us to convert these error values into a readable string format, making it easier to handle and display error messages to users. In our example, we used the ‘xlErrDiv0’ error, which is a built-in error code in Excel for dividing by zero. However, the CVErr function can be used with any error code or error value.

Identifying and Handling Error Values

Description: The CVErr function can also be used to check for and handle error values in a more systematic way. This helps in identifying and debugging errors in code.

On Error GoTo ErrorHandler
Dim errorValue As Variant
errorValue = CVErr(xlErrValue)
ErrorHandler:
If errorValue  Is CVErr(xlErrValue) Then
MsgBox "Error found: " & CStr(errorValue)
'Code to handle the error
End If

• Firstly, we use the ‘On Error GoTo’ statement to redirect any error that occurs to the ‘ErrorHandler’ section of our code.
• We then declare our ‘errorValue’ variable and assign an error value to it using the CVErr function.
• In the ‘ErrorHandler’ section, we use the ‘If’ statement to check if our error value matches the ‘xlErrValue’ error code. If it does, we display a message box with the error value.
• Lastly, we can add code to handle the error, such as displaying a specific error message or fixing the code causing the error.

Explanation:

The CVErr function can be used in conjunction with the ‘On Error’ statement to handle errors in a more organized manner. In this example, we are redirecting any error that occurs to the ‘ErrorHandler’ section, where we can check if it is an error value using the ‘If’ statement. This helps in identifying and debugging errors more efficiently by providing a specific error message and handling the error in a structured manner.

Using Custom Error Values

Description: Besides built-in error codes, the CVErr function can also be used with custom error values created by the user. This is helpful when working with specific error conditions and messages.

Dim errorValue As Variant
errorValue = CVErr(100)
'Code to check for specific error condition
If [specific condition] Then
errorValue = CVErr(101)
End If
MsgBox "Error value: " & CStr(errorValue)

• Firstly, we declare our ‘errorValue’ variable and assign it a custom error value of 100 using the CVErr function.
• We then have code to check for a specific condition and if it is met, we assign a different custom error value (101) to our errorValue variable.
• Lastly, we display the error value in a message box using the CStr function. Depending on the specific condition, the error value will either be 100 or 101.

Explanation:

The CVErr function allows us to create and use custom error values in our code. This is particularly useful when working with specific error conditions that require a different error message or handling method. By assigning different custom error values, we can easily differentiate between different types of errors and provide more specific error messages to users.

Conclusion:

The CVErr function is a handy tool in VBA for handling and displaying error messages. By converting error values into a string format, we can make them easier to understand and handle for users. Additionally, the ability to use custom error values with the CVErr function allows for more efficient handling of specific error conditions. Understanding and using this function can help improve the efficiency and effectiveness of our VBA code.