Microsoft Office Tutorials and References
In Depth Information
Function DIVIDETWO(num1, num2)
On Error GoTo ErrHandler
DIVIDETWO = num1 / num2
DIVIDETWO = “ERROR”
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 handling 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 actual error value.
Chapter 25 contains an example that demonstrates how to return an actual error value
from a function.
Many of the custom functions that you develop will work with the data contained in a cell or in a
range of cells. Recognize that a range can be a single cell or a group of cells. This section
describes some key concepts to make this task easier. The information in this section is intended
to be practical, rather than comprehensive. If you want more details, consult Excel’s online help.
Chapter 25 contains many practical examples of functions that use ranges. Studying
those examples helps to clarify the information in this section.
The For Each-Next construct
Your Function procedures often need to loop through a range of cells. For example, you may
write a function that accepts a range as an argument. Your code needs to examine each cell in
the range and do something. The For Each-Next construct is very useful for this sort of thing.
The syntax of the For Each-Next construct is
For Each element In group