Using Worksheet Error Values

Names and cell formulas in Microsoft Excel worksheets can contain one of seven error values: #DIV/0!, #N/A, #NAME?, #NULL!, #NUM!, #REF!, or #VALUE!. You can pass one of these error values from a worksheet to a procedure only if the passed argument is a Variant variable. To work with these error values, you can test the arguments using the IsError function and treat the error values as if they were user-defined error values (described in the preceding section).

You can also pass an error value from a user-defined function back to cells on a worksheet, as shown in the following example.

Function Commission(SharesSold, PricePerShare)
    If Not (IsNumeric(SharesSold) And IsNumeric(PricePerShare)) Then
        Commission = CVErr(xlErrNum)        ' xlErrNum corresponds to the                                             ' #NUM! error value.
        Exit Function
    Else
        TotalSalePrice = SharesSold * PricePerShare
        If TotalSalePrice <= 15000 Then
            Commission = 25 + 0.03 * SharesSold
        Else
            Commission = 25 + 0.03 * (0.9 * SharesSold)
        End If
    End If
End Function

You work with Microsoft Excel worksheet error values the same way you work with user-defined error values. However, as you can see in the following table, Visual Basic provides two ways to refer to worksheet error values: built-in constants that represent the error numbers, and literal error values (which must be enclosed in square brackets).

If you want to use the literal error value for #NUM! in the preceding example, just substitute [#NUM!] for CVErr(xlErrNum), as shown in the following code.

If Not (IsNumeric(SharesSold) And IsNumeric(PricePerShare)) Then
    Commission = [#NUM!]
    Exit Function
.
.
.