In VBA, If Not IsError(value) Then means “run this code if value is not an error value.” Use it to test a Variant that may contain an Excel error such as #N/A before you read, convert, or otherwise use that value. It does not catch every VBA runtime error; that requires separate error handling.
What “If Not IsError” means in VBA
IsError is a built-in VBA function. It returns True when the expression you pass it is an error value, and False otherwise. Not reverses that Boolean result. Therefore, If Not IsError(value) Then runs the Then branch when the value is safe from this particular kind of check: it is not an error value.
This is especially useful because an Excel cell containing a formula error can be read into VBA as a Variant holding an error value. For instance, a cell displaying #DIV/0! or #N/A is not simply ordinary text that happens to look like an error. If you try to convert or process that Variant as though it were a normal number or string, VBA may raise a type mismatch. Testing first lets your code choose a sensible response.
Do not confuse this check with a test for blank cells, zero, or text. An empty cell, the number 0, and the literal text #N/A are not error values, so IsError returns False for them. It recognizes an actual error value, not a particular appearance on the worksheet.
Basic syntax and control flow
The basic structure is a normal VBA If statement with Not IsError as its condition:
If Not IsError(expression) Thenstarts the branch for a non-error value.Elseis optional and can handle the error-value case.End Ifcloses a multiline If statement.
The expression can be a Variant variable, a cell’s value, or the result of a function that returns a Variant. Parentheses around the argument are required as part of the function call; the condition itself does not need extra parentheses. You can also write If IsError(value) = False Then, but If Not IsError(value) Then is the familiar, concise form.
Before running any macro, save a copy of the workbook. VBA macros cannot be undone with Ctrl+Z, so test code on a copy when it could change workbook data.
Sub CheckCellForError()
' Read the cell as a Variant so an Excel error can be tested.
Dim target As Range
Dim valueToCheck As Variant
Set target = ThisWorkbook.Worksheets("Sheet1").Range("A1")
valueToCheck = target.Value
If Not IsError(valueToCheck) Then
MsgBox "Cell value: " & CStr(valueToCheck)
Else
MsgBox "Cell contains an Excel error."
End If
End Sub
In this example, the test occurs before CStr converts the value to text. If A1 contains an error, the code takes the Else branch and never attempts that conversion. Change Sheet1 and A1 to match the workbook. The variable is declared as Variant deliberately: a Variant can hold numbers, text, empty values, and Excel error values.
Checking worksheet values safely
When reading a single cell, assign its value to a Variant and test that variable. This makes the possible error state explicit and avoids repeatedly reading the cell. The same pattern is useful when a formula may return an error depending on its inputs, or when a workbook is supplied by someone else and its contents are not fully controlled.
You can decide what to do in each branch rather than merely displaying a message. For example, a reporting macro could skip an invalid result, record a note in another column, or substitute a clearly chosen default. The right response depends on what the error means for the task. Replacing every error with zero may make later totals look plausible while concealing missing or invalid source data.
For a multi-cell range, Range.Value can return a two-dimensional Variant array rather than one value. Test individual elements of that array, such as the value at a particular row and column, instead of assuming that IsError will inspect every element for you. A loop can check each item and handle error values one at a time. Also remember that a cell’s displayed formatting does not determine its value: a number formatted to look like text is still numeric, while an actual formula error remains an error value.
Using IsError with lookup results
A common application is checking whether a lookup found a match. With VBA’s Application.VLookup, a failed lookup can be returned as an error value, so storing the result in a Variant and checking it with IsError is a practical pattern. The following example uses an exact match and assumes the lookup key is in the first column of the Products range.
Sub CheckProductLookup()
' Keep the lookup result in a Variant so it can hold an error value.
Dim lookupResult As Variant
lookupResult = Application.VLookup("P-104", _
ThisWorkbook.Worksheets("Products").Range("A2:C100"), 3, False)
If Not IsError(lookupResult) Then
MsgBox "Lookup result: " & CStr(lookupResult)
Else
MsgBox "No matching product was found."
End If
End Sub
The fourth argument, False, requests an exact match. The third argument, 3, returns the value from the third column of the specified range. If a match is found, the message shows that returned value; if no match is found, the error result takes the Else branch. This handles the lookup result, but it does not guarantee that every other part of the macro is free of runtime errors—for example, the Products sheet must exist.
There is an important VBA distinction here. Calling Application.WorksheetFunction.VLookup can raise a VBA runtime error when the lookup fails rather than giving you an error Variant to test afterward. In that case, execution may stop at the lookup line before IsError runs. If you want to inspect a failed lookup result with IsError, use the Application.VLookup form shown above, or use an appropriate VBA error handler.
IsError versus VBA error handling
IsError checks a value that is already an error value. It is not a general-purpose shield around a line of code. It will not catch problems such as trying to open a missing file, referring to a nonexistent worksheet, or dividing by zero in a VBA expression. Those operations can raise runtime errors, which are handled with VBA’s On Error statement and an error-handling routine.
In short, ask what kind of problem you have. If a function or worksheet cell has produced an error value that you can store and inspect, IsError is appropriate. If VBA itself raises an error while carrying out an operation, use error handling. The two approaches can appear in the same procedure, but they address different stages: a value test examines a result, while an error handler responds to an exception during execution.
Be careful not to use On Error Resume Next as a substitute for checking every result. It can allow execution to continue after an unexpected failure, leaving later code to use incomplete or stale data. If you use it for a narrowly defined operation, check Err.Number, handle the outcome, and restore normal handling with On Error GoTo 0 when appropriate.
Common mistakes and edge cases
- Using a String variable too early. A cell error may fail during assignment to a String. Read the value into a Variant, check it, and only then convert it if appropriate.
- Checking text that looks like an error. The text
"#N/A"is not the same as an Excel#N/Aerror value.IsErrorreturns False for ordinary text. - Expecting it to check a formula’s inputs.
IsErrortests the expression you pass. It does not inspect every precedent cell or explain why a formula returned an error. - Putting a risky test after And. VBA’s
Andoperator does not short-circuit. In a condition such asIf Not IsError(v) And v > 0 Then, VBA can still evaluatev > 0whenvis an error value. Use nested If statements so the second test runs only after the first succeeds. - Assuming every failed function returns an error value. Some VBA calls raise runtime errors instead. Determine how the specific call behaves before choosing
IsErroror an error handler.
The non-short-circuit detail matters in practice. If the first condition is intended to protect the second, write two levels of control flow: first test Not IsError(v), then, inside that branch, compare v with zero. That structure is clear to readers and prevents the comparison from being evaluated for an error value.
Choosing the right check for the job
Use IsError when the thing you need to decide about is an error-valued result. Use IsEmpty to test whether a Variant is Empty, and use a separate test for a blank cell if that is the rule your procedure needs. A cell containing a formula that returns an empty string is not necessarily equivalent to a genuinely empty cell. Use a type or value check when you need to distinguish numbers, text, dates, or other data types; IsError does not make those distinctions.
It is often useful to handle different conditions separately. A lookup result might be an error because there was no match, while a successful match could return a blank value. Those outcomes may need different treatment. Decide the expected cases first, then write a branch for each one rather than treating all unusual results as the same problem. This makes the macro easier to maintain and makes it less likely that a data issue will be silently hidden.
Frequently Asked Questions
What does If Not IsError mean in VBA?
It means to execute the If branch when the supplied expression is not an error value. IsError returns a Boolean, and Not reverses it.
Does IsError detect #N/A in a cell?
Yes, if the cell contains the actual Excel #N/A error and you pass its value to IsError. It returns False for the literal text "#N/A".
Is IsError the same as On Error Resume Next?
No. IsError tests an error value that has already been returned. On Error Resume Next changes how VBA responds to runtime errors raised while code runs, and should be used carefully.
Why does IsError not catch my VLookup failure?
If the call uses Application.WorksheetFunction.VLookup, a missing match can raise a runtime error before the next line executes. Try Application.VLookup when you need to inspect the returned error value, or handle the runtime error explicitly.
Can I combine IsError with another condition using And?
Do not rely on the first part of an And condition to prevent evaluation of the second part. VBA evaluates both sides. Use nested If statements when the second test could fail or be invalid for an error value.
Related
- How to Merge Cells in Excel: Steps, Options, and Fixes
- Split Cell Contents Into Multiple Columns in Excel
- Excel Conditional Formatting: Find Partial Text Matches
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Find Sheet Names in a Workbook: Practical MethodsTo find worksheet names with VBA, loop through the workbook’s Worksheets collection and read each sheet’s Name property.…
- Access VBA: Open an Excel File and Work With Its DataYes. Access VBA can open an Excel workbook by controlling the Excel application, or it can import worksheet…
- Word VBA: Remove Formatting While Keeping StylesTo remove direct formatting from selected text in Word VBA while keeping its assigned styles, reset both its…
- Excel VBA: Check Whether a String Is Empty or BlankIn Excel VBA, use Len(text) = 0 to check whether a string has no characters. If you also…
- Excel VBA: How to Break Out of an Endless LoopTo stop a running Excel VBA loop on Windows, press Ctrl+Break (or Ctrl+Pause on keyboards with that key),…
- Excel VBA Format Date as DD/MM/YYYY in a WorksheetTo display Excel dates as dd/mm/yyyy with VBA, set the range’s NumberFormat property, for example: Range("B2:B100").NumberFormat = "dd/mm/yyyy".…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.