Cell Error Values

You can insert a cell error value into a cell or you can test the value of a cell for an error value using the CVErr function. The cell error values have constants assigned to them as shown in the following table.

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!


Example

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.


Worksheets("Sheet1").Activate
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