Microsoft Office Tutorials and References
In Depth Information
Chapter 26 contains an example that demonstrates how to return an actual error value
from a function.
Most 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 26 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
The following Function procedure accepts a range argument and returns the sum of the squared values in the
Function SUMOFSQUARES(rng as Range)
Dim total as Double
Dim cell as Range
total = 0
For Each cell In rng
total = total + cell ^ 2
SUMOFSQUARES = total