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
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”
AreaType = “Block”
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.