Microsoft Office Tutorials and References

In Depth Information

Figure 25-2:
Use the Intersect function to work with the intersection of two ranges.

The following Function procedure accepts two range arguments and returns the count of the number of cells

that the ranges have in common:

Function CELLSINCOMMON(rng1, rng2)

Dim CommonCells As Range

On Error Resume Next

Set CommonCells = Intersect(rng1, rng2)

If Err.Number = 0 Then

CELLSINCOMMON = CommonCells.CountLarge

Else

CELLSINCOMMON = 0

End If

End Function

The CELLSINCOMMON function uses the Intersect function to create a range object named CommonCells.

Note the use of On Error Resume Next. This statement is necessary because the Intersect function returns an er-

ror if the ranges have no cells in common. If the error occurs, it is ignored. The final statement checks the Num-

ber property of the Err object. If it is 0, no error occurs, and the function returns the value of the CountLarge

property for the CommonCells object. If an error does occur, Err.Number has a value other than 0, and the func-

tion returns 0.

The Union function

The Union function combines two or more ranges into a single range. The following statement uses the Union

function to create a range object that consists of the first and third columns of a worksheet:

Set TwoCols = Union(Range(“A:A”), Range(“C:C”))

The Union function takes between 2 and 30 arguments.

The UsedRange property

The UsedRange property returns a Range object that represents the used range of the worksheet. Press Ctrl+End

to activate the lower-right cell of the used range. The UsedRange property can be
very useful
in making your

functions more efficient.

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