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:

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`:

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

For serious VBA development, it’s crucial to implement structured error handling to make your macros robust and easier to maintain.

facebooklinkedin