Microsoft Office Tutorials and References

In Depth Information

**Working with Ranges**

Following is a function named
AreaType
, which returns a text string that describes the type of

range selection.

Function AreaType(RangeArea As Range) As String

‘ Returns the type of a range in an area

Select Case True

Case RangeArea.Cells.CountLarge = 1

AreaType = “Cell”

Case RangeArea.CountLarge = Cells.CountLarge

AreaType = “Worksheet”

Case RangeArea.Rows.Count = Cells.Rows.Count

AreaType = “Column”

Case RangeArea.Columns.Count = Cells.Columns.Count

AreaType = “Row”

Case Else

AreaType = “Block”

End Select

End Function

This function accepts a
Range
object as its argument and returns one of five strings that

describe the area:
Cell
,
Worksheet
,
Column
,
Row
, or
Block
. The function uses a
Select

Case
construct to determine which of five comparison expressions is
True
. For example, if the

range consists of a single cell, the function returns
Cell
. If the number of cells in the range is

equal to the number of cells in the worksheet, it returns
Worksheet
. If the number of rows in the

range equals the number of rows in the worksheet, it returns
Column
. If the number of columns

in the range equals the number of columns in the worksheet, the function returns
Row
. If none of

the
Case
expressions is
True
, the function returns
Block
.

Notice that I used the
CountLarge
property when counting cells. As I noted previously in this

chapter, the number of selected cells could potentially exceed the limit of the
Count
property.

This example is available on the companion CD-ROM in a file named
about range

selection.xlsm
. The workbook contains a procedure (named
RangeDescription
)

that uses the
AreaType
function to display a message box that describes the current

range selection. Figure 11-7 shows an example. Understanding how this routine works

will give you a good foundation for working with
Range
objects.

You might be surprised to discover that Excel allows multiple selections to be identical.

For example, if you hold down Ctrl and click five times in cell A1, the selection will have

five identical areas. The
RangeDescription
procedure takes this possibility into

account, and doesn’t count the same cell multiple times.