How to use Error function in VBA?

The Error function in VBA, sometimes confused with the Err object, isn’t directly relevant to error handling. Instead, the Error function is used to return the error message that corresponds to a given error number. However, when you’re handling errors in VBA, you often use the Err object, which gives you information about runtime errors. The Err object has several properties and methods, including .Number which gives you the error code, and .Description which provides the error description. If you want to use the Error function to get an error description based on an error number, you can do it as follows:

VBA


Sub ExampleErrorFunction()
    Dim errorNumber As Long
    Dim errorDescription As String
    
    ' Example error number
    errorNumber = 13 ' Type Mismatch
    
    ' Get the error description associated with the error number
    errorDescription = Error(errorNumber)
    
    ' Display the error description
    MsgBox "The error description for error number " & errorNumber & " is: " & errorDescription
End Sub

Keep in mind that if you pass an error number to the Error function that does not correspond to a defined VBA error, you will get a message stating that the application or object-defined error occurred. To handle runtime errors, you use error handling constructs like On Error GoTo:

VBA


Sub ExampleErrorHandling()
    On Error GoTo ErrorHandler

    ' Your code here that might cause errors
    Dim result As Variant
    result = 1 / 0 ' This will cause a divide by zero error

    ' If there's no error, you can skip the error handling block
    GoTo CleanUp

ErrorHandler:
    ' Display error information from the Err object
    MsgBox "An error occurred: " & Err.Number & " " & Err.Description

CleanUp:
    ' Any cleanup code would go here

    ' Resume execution or end the sub
    On Error GoTo 0 ' Clear the error handling
End Sub