How to use IsError function in VBA?

In VBA (Visual Basic for Applications), the IsError function is used to check if an expression results in an error. This can be particularly useful when you are performing operations that might fail under certain conditions, such as looking up a value in a range or performing a calculation with potentially invalid input. Here’s how you can use the IsError function in VBA:

Basic Usage

VBA


Sub CheckForError()
    Dim result As Variant
    Dim isThereAnError As Boolean
    
    ' Perform some calculation that might result in an error
    On Error Resume Next ' This is to prevent VBA from stopping execution if an error is encountered
    result = Application.WorksheetFunction.VLookup("NonExistentValue", Range("A1:B10"), 2, False)
    On Error GoTo 0 ' Resume normal error handling
    
    ' Use IsError to check if result contains an error
    isThereAnError = IsError(result)
    
    If isThereAnError Then
        MsgBox "There was an error in the calculation!"
    Else
        MsgBox "The result of the calculation is " & result
    End If
End Sub

In this example, the IsError function is used to check if the VLookup function returned an error because it didn’t find the value “NonExistentValue” in the specified range. If IsError returns True, a message box is displayed indicating that there was an error. Note that the use of On Error Resume Next and On Error GoTo 0 around the potentially error-prone code is good practice to prevent the VBA macro from halting execution upon encountering an error. Instead, it allows the IsError function to be used to handle the error as part of the normal flow of execution.

facebooklinkedin