Determining the type of selected range

Excel supports several types of range selections:

■ A contiguous range of cells

■ One or more entire columns

■ One or more entire rows

■ The entire worksheet

■ Any combination of the above (that is, a multiple selection)

As a result, when your VBA procedure processes a user-selected range, you can't make any presumptions about what that range might be.

In the case of a multiple range selection, the Range object comprises separate areas. To determine whether a selection is a multiple selection, use the Areas method, which returns an Areas collection. This collection represents all the ranges within a multiple range selection.

You can use an expression like the following to determine whether a selected range has multiple areas:

NumAreas = Selection.Areas.Count

If the NumAreas variable contains a value greater than 1, the selection is a multiple selection.

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 in Excel 2007 could potentially exceed the limit of the Count property. CD-ROM

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.

4

1

n ;

n

Hi

-

Li

-./ Yia .1 n. r Jr 1 b

c«i owk* i Teal Cab 1

21

i

W

12

33

I

Figure 11-7: A VBA procedure analyzes the currently selected range. Note

Figure 11-7: A VBA procedure analyzes the currently selected range. Note

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 into account and does not count the same cell multiple times.

0 0

Responses

Post a comment