Microsoft Office Tutorials and References
In Depth Information
On Error Resume Next
If you use this statement, you can determine whether an error occurs by checking the Number property of the
Err object. If this property is equal to zero, an error did not occur. If Err.Number is equal to anything else, an er-
ror did occur.
The following example is a function that returns the name of a cell or range. If the cell or range does not have a
name, an error occurs, and the formula that uses the function returns a #VALUE! error.
Function RANGENAME(rng)
RANGENAME = rng.Name.Name
End Function
The following list shows an improved version of the function. The On Error Resume Next statement causes
VBA to ignore the error. The If Err statement checks whether an error occurs. If so, the function returns an
empty string.
Function RANGENAME(rng)
On Error Resume Next
RANGENAME = rng.Name.Name
If Err.Number <> 0 Then RANGENAME = “”
End Function
The following statement instructs VBA to watch for errors; if an error occurs, it continues executing at a differ-
ent named location — in this case, a statement labeled ErrHandler:
On Error GoTo ErrHandler
The following Function procedure demonstrates this statement. The DIVIDETWO function accepts two argu-
ments (num1 and num2) and returns the result of num1 divided by num2.
Function DIVIDETWO(num1, num2)
On Error GoTo ErrHandler
DIVIDETWO = num1 / num2
Exit Function
ErrHandler:
DIVIDETWO = “ERROR”
End Function
The On Error GoTo statement instructs VBA to jump to the statement labeled ErrHandler if an error occurs. As
a result, the function returns a string (ERROR) if any type of error occurs while the function is executing. Note
the use of the Exit Function statement. Without this statement, the code continues executing, and the error hand-
ling code always executes. In other words, the function always returns ERROR.
It's important to understand that the DIVIDETWO function is nonstandard in its approach. Returning an error
message string when an error occurs (ERROR) is not how Excel functions work. Excel functions return an actu-
al error value.
Search JabSto ::




Custom Search