CVErr Function

Returns a Variant of subtype Error containing an error number.

Syntax

CVErr(errornumber)

Parameters

errornumber: Required. Long integer that identifies an error. The valid range is from 0 to 65535, though application-defined errors are typically in the range 513-65535 (VB6 uses 1-512 for system errors).

Return Value

Returns a Variant of subtype Error (VarType = 10) containing the specified error number. This is not the same as raising an error with Err.Raise; instead, it creates an Error value that can be assigned to variables and returned from functions.

Remarks

The CVErr function is used to create user-defined error values that can be returned from functions or assigned to Variant variables. This is particularly useful for: - Returning error conditions from functions without raising exceptions - Creating functions that behave like Excel worksheet functions (returning error values) - Signaling invalid results that should propagate through calculations - Implementing error handling in data processing pipelines Important Characteristics: - Returns a Variant of subtype Error (not an exception) - Error values propagate through expressions - Can be tested with IsError() function - Not the same as Err object or Err.Raise - Commonly used with VBA functions called from Excel - Error values cannot be used in arithmetic operations - VarType of CVErr result is 10 (vbError)

Error Number Ranges

Range	Description
0-512	Reserved for VB6 system errors
513-65535	Available for application-defined errors
2007-2042	Excel error values (when used in Excel automation)

Excel Error Constants

When creating functions for Excel, these error values are commonly used:

Constant	Value	Excel Display	Meaning
xlErrDiv0	2007	#DIV/0!	Division by zero
xlErrNA	2042	#N/A	Value not available
xlErrName	2029	#NAME?	Invalid name
xlErrNull	2000	#NULL!	Null intersection
xlErrNum	2036	#NUM!	Invalid number
xlErrRef	2023	#REF!	Invalid reference
xlErrValue	2015	#VALUE!	Wrong type

Examples

Basic Usage

' Create an error value
Dim result As Variant
result = CVErr(2042)  ' Create #N/A error
' Test if value is an error
If IsError(result) Then
    MsgBox "Result is an error"
End If

Function Returning Error on Invalid Input

Function SafeDivide(numerator As Double, denominator As Double) As Variant
    If denominator = 0 Then
        SafeDivide = CVErr(2007)  ' #DIV/0!
    Else
        SafeDivide = numerator / denominator
    End If
End Function
' Usage
Dim result As Variant
result = SafeDivide(10, 0)
If IsError(result) Then
    MsgBox "Division error occurred"
Else
    MsgBox "Result: " & result
End If

Excel UDF with Error Handling

Function Lookup(value As Variant, table As Range) As Variant
    Dim cell As Range
    ' Validate input
    If IsEmpty(value) Then
        Lookup = CVErr(2042)  ' #N/A
        Exit Function
    End If
    ' Search for value
    For Each cell In table
        If cell.Value = value Then
            Lookup = cell.Offset(0, 1).Value
            Exit Function
        End If
    Next cell
    ' Not found
    Lookup = CVErr(2042)  ' #N/A
End Function

Common Patterns

Error Constants Definition

' Define Excel error constants
Public Const xlErrDiv0 As Long = 2007   ' #DIV/0!
Public Const xlErrNA As Long = 2042     ' #N/A
Public Const xlErrName As Long = 2029   ' #NAME?
Public Const xlErrNull As Long = 2000   ' #NULL!
Public Const xlErrNum As Long = 2036    ' #NUM!
Public Const xlErrRef As Long = 2023    ' #REF!
Public Const xlErrValue As Long = 2015  ' #VALUE!
' Use in functions
Function MyFunction(input As Variant) As Variant
    If Not IsNumeric(input) Then
        MyFunction = CVErr(xlErrValue)
    Else
        MyFunction = input * 2
    End If
End Function

Validation Functions

Function ValidateRange(value As Double, min As Double, max As Double) As Variant
    If value < min Or value > max Then
        ValidateRange = CVErr(2036)  ' #NUM! - Number out of range
    Else
        ValidateRange = value
    End If
End Function

Error Propagation

Function Calculate(input As Variant) As Variant
    ' Check if input is already an error
    If IsError(input) Then
        Calculate = input  ' Propagate the error
        Exit Function
    End If
    ' Perform calculation
    If input < 0 Then
        Calculate = CVErr(2036)  ' #NUM!
    Else
        Calculate = Sqr(input)
    End If
End Function

Database Lookup with Error

Function GetEmployeeName(employeeID As Long) As Variant
    Dim rs As ADODB.Recordset
    On Error GoTo ErrorHandler
    Set rs = New ADODB.Recordset
    rs.Open "SELECT Name FROM Employees WHERE ID = " & employeeID, conn
    If rs.EOF Then
        GetEmployeeName = CVErr(2042)  ' #N/A - Not found
    Else
        GetEmployeeName = rs("Name")
    End If
    rs.Close
    Set rs = Nothing
    Exit Function
ErrorHandler:
    GetEmployeeName = CVErr(2042)  ' #N/A
End Function

Array Formula with Errors

Function ProcessArray(values As Variant) As Variant
    Dim results() As Variant
    Dim i As Long
    If Not IsArray(values) Then
        ProcessArray = CVErr(2015)  ' #VALUE!
        Exit Function
    End If
    ReDim results(LBound(values) To UBound(values))
    For i = LBound(values) To UBound(values)
        If IsError(values(i)) Then
            results(i) = values(i)  ' Propagate error
        ElseIf Not IsNumeric(values(i)) Then
            results(i) = CVErr(2015)  ' #VALUE!
        Else
            results(i) = values(i) * 2
        End If
    Next i
    ProcessArray = results
End Function

Type Conversion with Error

Function SafeCLng(value As Variant) As Variant
    On Error GoTo ErrorHandler
    If IsError(value) Then
        SafeCLng = value  ' Propagate error
    ElseIf IsEmpty(value) Then
        SafeCLng = CVErr(2042)  ' #N/A
    ElseIf Not IsNumeric(value) Then
        SafeCLng = CVErr(2015)  ' #VALUE!
    Else
        Dim temp As Double
        temp = CDbl(value)
        If temp < -2147483648# Or temp > 2147483647# Then
            SafeCLng = CVErr(2036)  ' #NUM!
        Else
            SafeCLng = CLng(value)
        End If
    End If
    Exit Function
ErrorHandler:
    SafeCLng = CVErr(2015)  ' #VALUE!
End Function

Conditional Error Return

Function GetDiscount(totalSales As Double) As Variant
    Select Case totalSales
        Case Is < 0
            GetDiscount = CVErr(2036)  ' #NUM! - Negative sales
        Case 0 To 999.99
            GetDiscount = 0
        Case 1000 To 4999.99
            GetDiscount = 0.05
        Case 5000 To 9999.99
            GetDiscount = 0.1
        Case Is >= 10000
            GetDiscount = 0.15
        Case Else
            GetDiscount = CVErr(2042)  ' #N/A
    End Select
End Function

Error Checking Helper

Function GetErrorNumber(value As Variant) As Long
    ' Returns error number or 0 if not an error
    If IsError(value) Then
        ' There's no direct way to extract error number in VB6
        ' Would need to compare against known error values
        GetErrorNumber = -1  ' Indicates error present
    Else
        GetErrorNumber = 0
    End If
End Function

Advanced Usage

Custom Error Type System

' Application-specific error codes
Public Const APP_ERR_INVALID_USER As Long = 1000
Public Const APP_ERR_DATABASE As Long = 1001
Public Const APP_ERR_NETWORK As Long = 1002
Public Const APP_ERR_PERMISSION As Long = 1003
Function AuthenticateUser(username As String, password As String) As Variant
    If Len(username) = 0 Then
        AuthenticateUser = CVErr(APP_ERR_INVALID_USER)
        Exit Function
    End If
    ' Check credentials
    If Not ValidateCredentials(username, password) Then
        AuthenticateUser = CVErr(APP_ERR_PERMISSION)
        Exit Function
    End If
    ' Return user object on success
    AuthenticateUser = GetUserObject(username)
End Function

Error Value in Collections

Function ProcessRecords() As Collection
    Dim results As New Collection
    Dim rs As ADODB.Recordset
    Dim i As Long
    Set rs = GetRecords()
    While Not rs.EOF
        On Error Resume Next
        results.Add ProcessRecord(rs)
        If Err.Number <> 0 Then
            results.Add CVErr(2015)  ' Add error marker
            Err.Clear
        End If
        rs.MoveNext
    Wend
    Set ProcessRecords = results
End Function

Chainable Operations with Error Propagation

Function Step1(input As Variant) As Variant
    If IsError(input) Then
        Step1 = input
    ElseIf input < 0 Then
        Step1 = CVErr(2036)
    Else
        Step1 = Sqr(input)
    End If
End Function
Function Step2(input As Variant) As Variant
    If IsError(input) Then
        Step2 = input
    ElseIf input = 0 Then
        Step2 = CVErr(2007)
    Else
        Step2 = 100 / input
    End If
End Function
' Chain operations
result = Step2(Step1(value))

Error Handling

Function CreateErrorSafe(errorNum As Long) As Variant
    On Error GoTo ErrorHandler
    If errorNum < 0 Or errorNum > 65535 Then
        CreateErrorSafe = CVErr(2036)  ' Invalid error number
    Else
        CreateErrorSafe = CVErr(errorNum)
    End If
    Exit Function
ErrorHandler:
    CreateErrorSafe = CVErr(2042)  ' Generic error
End Function

Common Errors

Error 13 (Type mismatch): Error number is not a valid Long integer
Error 6 (Overflow): Error number is outside valid range (0-65535)

Performance Considerations

CVErr is a fast function with minimal overhead
Error values are lightweight Variant subtypes
Using CVErr is more efficient than raising and catching exceptions
Error propagation through calculations is automatic
No significant memory overhead compared to other Variant values

Comparison with Other Error Mechanisms

`CVErr` vs `Err.Raise`

' CVErr - Returns error value (doesn't stop execution)
Function Method1(x As Double) As Variant
    If x < 0 Then
        Method1 = CVErr(2036)  ' Returns error, continues
    Else
        Method1 = Sqr(x)
    End If
End Function
' Err.Raise - Throws exception (stops execution)
Function Method2(x As Double) As Double
    If x < 0 Then
        Err.Raise 5, , "Invalid argument"  ' Stops execution
    Else
        Method2 = Sqr(x)
    End If
End Function

CVErr advantages: - Doesn't interrupt program flow - Can be used in expressions - Natural for functional-style programming - Compatible with Excel worksheet functions Err.Raise advantages: - Forces immediate attention to errors - Provides error description and source - Traditional exception handling model - Better for critical errors

Best Practices

Always Check for Errors Before Using Values

Dim result As Variant
result = SomeFunction()
If IsError(result) Then
    MsgBox "Error occurred"
Else
    ' Safe to use result
    Debug.Print result
End If

Use Meaningful Error Numbers

' Good - Use named constants
Const ERR_INVALID_INPUT As Long = 1000
result = CVErr(ERR_INVALID_INPUT)
' Avoid - Magic numbers
result = CVErr(42)  ' What does 42 mean?

Document Custom Error Codes

' Application Error Codes (1000-1999)
Public Const ERR_INVALID_USER As Long = 1000    ' Invalid username
Public Const ERR_EXPIRED_SESSION As Long = 1001  ' Session expired
Public Const ERR_INSUFFICIENT_RIGHTS As Long = 1002  ' Access denied

Limitations

Cannot extract error number from error value directly in VB6
Error values cannot be used in arithmetic operations
Limited to Long integer error numbers (0-65535)
No built-in error description with CVErr (unlike Err object)
VarType test required to detect errors (IsError function)
Not all VB6 functions handle error values gracefully

IsError: Tests if a Variant contains an error value
Err.Raise: Raises a runtime error (different from CVErr)
Error: Returns error message for an error number
Error$: String version of Error function
VarType: Returns the subtype of a Variant (10 for Error)