Microsoft Office Tutorials and References

In Depth Information

**Using Ranges**

You can also write this function without using an
If-Then-Else
construct. In the following

function, the expression to the right of the equal sign returns either TRUE or FALSE — and this

value is returned by the function:

Function CELLISHIDDEN(cell)

CELLISHIDDEN = cell.EntireRow.Hidden Or _

cell.EntireColumn.Hidden

End Function

The Set keyword

An important concept in VBA is the ability to create a new
Range
object and assign it to a

variable — more specifically, an
object variable.
You do so by using the
Set
keyword. The following

statement creates an object variable named
MyRange
:

Set MyRange = Range(“A1:A10”)

After the statement executes, you can use the
MyRange
variable in your code in place of the

actual range reference. Examples in subsequent sections help to clarify this concept.

Creating a
Range
object is not the same as creating a named range. In other words, you

can’t use the name of a
Range
object in your worksheet formulas.

The Intersect function

The
Intersect
function returns a range that consists of the intersection of two other ranges.

For example, consider the two ranges selected in Figure 24-2. These ranges, D3:D10 and B5:F5,

contain one cell in common (D5). In other words, D5 is the intersection of D3:D10 and B5:F5.

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