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.
Unlock Your Potential
Excel
Access
Power BI
