Microsoft Office Tutorials and References

In Depth Information

**Using Ranges**

Consider the following
Function
procedure. This function accepts a range argument and

returns the number of formula cells in the range:

Function FORMULACOUNT(rng)

cnt = 0

For Each cell In rng

If cell.HasFormula Then cnt = cnt + 1

Next cell

FORMULACOUNT = cnt

End Function

In many cases, the preceding function works just fine. But what if the user enters a formula like

this one?

=FORMULACOUNT(A:C)

The three-column argument consists of 3,145,728 cells. With an argument that consists of one or

more entire columns, the function does not work well because it loops through every cell in the

range, even those that are well beyond the area of the sheet thatâ€™s actually used. The following

function is rewritten to make it more efficient:

Function FORMULACOUNT(rng)

cnt = 0

Set WorkRange = Intersect(rng, rng.Parent.UsedRange)

If WorkRange Is Nothing Then

FORMULACOUNT = 0

Exit Function

End If

For Each cell In WorkRange

If cell.HasFormula Then cnt = cnt + 1

Next cell

FORMULACOUNT = cnt

End Function

This function creates a
Range
object variable named
WorkRange
that consists of the

intersection of the range passed as an argument and the used range of the worksheet. In other words,

WorkRange
consists of a subset of the range argument that only includes cells in the used range

of the worksheet. Note the
If-Then
construct that checks if the
WorkRange
is
Nothing
. That

will be the case if the argument for the function is outside of the used range. In such a case, the

function returns 0, and execution ends.