VBA 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.
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.
Code 1: Function
xlfReciprocal_1the reciprocal of Number: 1 / Number
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.
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
Code 2: Function
xlfReciprocal_2the reciprocal of Number: 1 / Number
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
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.
Code 3: Function
xlfReciprocal_3the reciprocal of Number: 1 / Number
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.
Code 4: Sub
TestxlfReciprocalthe reciprocal of Number: 1 / Number
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 1 - lower panel).
Cell Error Values
|Excel CVerror constant(number)||Cell error value|
|CVErr(errornumber)||Returns a Variant of subtype Error (Locals Window > Type: Variant/Error) containing an error number specified by the user|
|Goto line||Branches to a specified line within a procedure|
|On Error Goto line||Enables an error-handling routine and specifies the location of the routine within the procedure. Enables the error-handling routine that starts at specified line|
- This example was developed in Excel 2013 with VBA 7.1
- Revised: Wednesday 2nd of November 2016 - 09:12 AM, [Australian Eastern Standard Time (EST)]