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