UDF error handling

This module shows how to handle routine errors in user defined functions (UDFs) and other procedures. The simple example used is a function to return the reciprocal of a number, with the error raised when divide by zero occurs. Three variations of the function are developed in Code panes 1, 2, and 3. Without any specific error handling, all three return a #VALUE! error when used as worksheet functions. The analyst wants to replace the #VALUE! error with a #DIV/0! error.

Code 1

In Code 1, the divide by zero condition is tested by the If statement in  line 4 . If Number = 0 is TRUE, then the CVErr(xlErrDiv0) value is assigned to the function name xlfReciprocal_1 in  line 5. Execution then skips to  line 8.

Function xlfReciprocal_1(Number As Double) As Variant
'' Return the reciprocal of Number: 1 / Number

    If Number = 0 Then
        xlfReciprocal_1 = CVErr(xlErrDiv0)
    Else
        xlfReciprocal_1 = 1 / Number
    End If

End Function

The #DIV/0! error is code value 2007 (see the Cell Error Values table below) with an enumeration of xlErrDiv0. This value is converted to an error with the VBA CVErr function. CVErr returns a Variant with subtype Error, thus the function name is set to type Variant to allow this return type.

Code 2

This example uses formal error handling. In code 2  line 3, the VBA On Error Goto <line / label> statement transfers program control to the line with the label name - ErrHandler  line 7. This occurs in the case of a worksheet #VALUE! error, or a VBA Run-time error (shown in figure 1). All GoTo labels end with a : character. In cases where an error is not raised, the Exit Function statement in  line 6 suppresses execution of the error handler statement otherwise execution will always continue to the End Function statement

Public Function xlfReciprocal_2(Number As Double) As Variant
'' Return the reciprocal of Number: Number^(-1)
On Error GoTo ErrHandler
    xlfReciprocal_2 = Number ^ -1

Exit Function
ErrHandler:
    xlfReciprocal_2 = CVErr(xlErrDiv0)
End Function

an alternate version of line 8 is ...

    xlfReciprocal_2 = VBA.CVErr(XlCVError.xlErrDiv0)

where XlCVError displays the CVerror constants list (figure 1).

Code 3

The xlfReciprocal_3 function contains no error handling statements. In the case of a an error, it returns a #VALUE! error from the worksheet, and raises a Debug Error message when called from a VBA procedure - see code 4 for statement details.

Function xlfReciprocal_3(Number As Double) As Variant
'' Return the reciprocal of Number: 1 / Number

        xlfReciprocal_3 = 1 / Number

End Function									

The Debug Error is in figure 1 - top panel. It is called a Debug Error because the default button is Debug.

Sub TestxlfReciprocal()
Dim Ans1, Ans2, Ans3

    Ans1 = xlfReciprocal_1(0)
    Ans2 = xlfReciprocal_2(0)
    Ans3 = xlfReciprocal_3(0)

End Sub									

If the Analyst clicks the Debug button, the code window displays the Yellow step line at the place where the error was raised (figure 2 - lower panel).

References

Cell Error Values

VBA functions

VBA statements

• This example was updated with Excel version 2004 (build 12730.20270) and VBA 7.1 version 1097
• Revised: Saturday 25th of February 2023 - 09:37 AM, [Australian Eastern Standard Time (EST)]

Copyright © 2011 – 2023 ♦ Dr Ian O'Connor, CPA.   |   Privacy policy

Comment & Reply policy