Excel - Excel 2003 - VBA - Checking for Error in Cell.

Asked By Craig Brandt on 07-Aug-08 10:25 AM
How do I check a cell to see if there is a #N/A or #REF in a VBA routine?


tside replied on 09-Aug-08 11:16 PM
Use the IsError() function with a cell reference inside the
parentheses, such as IsError(Range("A1"))
Don Guillett replied on 07-Aug-08 10:46 AM
Cell Error Values
You can insert a cell error value into a cell or test the value of a cell
for an error value by using the CVErr function. The cell error values can be
one of the following XlCVError constants.

Constant Error number Cell error value
xlErrDiv0 2007 #DIV/0!
xlErrNA 2042 #N/A
xlErrName 2029 #NAME?
xlErrNull 2000 #NULL!
xlErrNum 2036 #NUM!
xlErrRef 2023 #REF!
xlErrValue 2015 #VALUE!

This example inserts the seven cell error values into cells A1:A7 on Sheet1.

myArray = Array(xlErrDiv0, xlErrNA, xlErrName, xlErrNull, _
xlErrNum, xlErrRef, xlErrValue)
For i = 1 To 7
Worksheets("Sheet1").Cells(i, 1).Value = CVErr(myArray(i - 1))
Next i
This example displays a message if the active cell on Sheet1 contains a
cell error value. You can use this example as a framework for a
cell-error-value error handler.

If IsError(ActiveCell.Value) Then
errval = ActiveCell.Value
Select Case errval
Case CVErr(xlErrDiv0)
MsgBox "#DIV/0! error"
Case CVErr(xlErrNA)
MsgBox "#N/A error"
Case CVErr(xlErrName)
MsgBox "#NAME? error"
Case CVErr(xlErrNull)
MsgBox "#NULL! error"
Case CVErr(xlErrNum)
MsgBox "#NUM! error"
Case CVErr(xlErrRef)
MsgBox "#REF! error"
Case CVErr(xlErrValue)
MsgBox "#VALUE! error"
Case Else
MsgBox "This should never happen!!"
End Select
End If

Don Guillett
Microsoft MVP Excel
SalesAid Software
Craig Brandt replied on 07-Aug-08 01:50 PM
I really appreciate the help.