Syntax. ERROR.TYPE(error_value)
Definition. This function returns a number corresponding to an error value in Excel. If no error exists in the cell or in the calculation, the function returns the #N/A error.
Arguments
error_value (required). The error value (either the actual error value in a cell or the result of a calculation) for which you want to find the error code
Background. You can use this function in an IF() function to replace the error value with a string explaining the error. To do this, you need to be familiar with the relationship between error values and return values (see Table 11-4).
Table 11-4. Error Values and Results of the ERROR.TYPE Function
Error Value | Return Value |
|---|---|
#NULL! |
|
#DIV/0! |
|
#VALUE! |
|
#REF! |
|
#NAME? |
|
#NUM! |
|
#N/A |
|
No error |
|
Tip
To display the error value in a different format, use a custom function that you can program with Microsoft Visual Basic for Applications (VBA).
Examples. The following examples illustrate how to use the ERROR.TYPE() function.
Conditional Formatting. Assume that you want to use conditional formats to display the background of cells that contain error values in different colors. Figure 11-2 shows an example of the ISERROR() function that highlights cells with errors in a color defined by the user. The critical error caused by the division by zero (for which the ERROR.TYPE() function returns 2) is highlighted in a second color, such as red.
Pay attention to the order of the conditions: If the ISERROR() check is performed first, the check for the divide-by-zero error will be ignored.
Custom Functions. If you repeatedly have to establish the relationship between error types (1 through 7) and error values (such as #DIV/0!), you can use a custom function to speed up and simplify this process. The following code shows a possible solution, and the implementation is shown in Figure 11-3.
Function ErrorDescription(Range As Range)
If WorksheetFunction.IsError(Range.Value) Then
Select Case CStr(Range.Value)
Case "Error 2000"
ErrorDescription = "Intersection is empty"
Case "Error 2007"
ErrorDescription= "Division by zero"
Case "Error 2015"
ErrorDescription = "Noncalculable expression"
Case "Error 2023"
ErrorDescription = "Lost reference"
Case "Error 2029"
ErrorDescription= "Name not defined"
Case "Error 2036"
ErrorDescription = "Number cannot be shown"
Case "Error 2042"
ErrorDescription = "Nonexistent value"
End Select
Else
ErrorDescription = "No error"
End If
End Function
Figure 11-3. The error is shown in the left column, and the evaluation (calculated with a custom function) is in the right column.