Working with Ranges

887

Part VI: Programming Excel with VBA

•The macro recorder doesn’t always generate the most efficient code. Often, you can use the recorder to create your macro and then edit the code to make it more efficient.

•I recommend that you use named ranges in your VBA code. For example, a reference such as Range (“Total”) is better than Range (“D45”). In the latter case, you need to modify the macro if you add a row above row 45.

•When you record macros that select ranges, pay close attention to relative versus absolute recording mode (see Chapter 39). The recording mode that you choose can drastically affect the way the macro operates.

•If you create a macro that loops through each cell in the current range selection, be aware that the user can select entire columns or rows. In such a case, you need to create a subset of the selection that consists only of nonblank cells. Or, you can work with cells in the worksheet’s used range (by using the UsedRange property).

•Be aware that Excel allows you to select multiple ranges in a worksheet. For example, you can select a range, press Ctrl, and then select another range. You can test for this in your macro and take appropriate actions.

The examples in the following sections demonstrate these points.

Copying a range

Copying a range is a frequent activity in macros. When you turn on the macro recorder (using absolute recording mode) and copy a range from A1:A5 to B1:B5, you get a VBA macro like this:

Sub CopyRange()

Range(“A1:A5”).Select

Selection.Copy

Range(“B1”).Select

ActiveSheet.Paste

Application.CutCopyMode = False

End Sub

This macro works, but it’s not the most efficient way to copy a range. You can accomplish exactly the same result with the following one-line macro:

Sub CopyRange2()

Range(“A1:A5”).Copy Range(“B1”)

End Sub

This code takes advantage of the fact that the Copy method can use an argument that specifies the destination. Useful information about properties and methods is available in the Help system.

888

Chapter 44: VBA Examples

The example demonstrates that the macro recorder doesn’t always generate the most efficient code. As you see, you don’t have to select an object to work with it. Note that CopyRange2 doesn’t select a range; therefore, the active cell doesn’t change when this macro is executed.

Copying a variable-size range

Often, you want to copy a range of cells in which the exact row and column dimensions are unknown.

Figure 44.1 shows a range on a worksheet. This range contains data that is updated weekly. Therefore, the number of rows changes. Because the exact range address is unknown at any given time, writing a macro to copy the range can be challenging.

FIGURE 44.1

This range can consist of any number of rows.

The macro that follows demonstrates how to copy this range from Sheet1 to Sheet2 (beginning at cell A1). It uses the CurrentRegion property, which returns a Range object that corresponds to the block of used cells surrounding a particular cell. This is equivalent to choosing Home Editing Find & Select Go To, clicking the Special button, and then selecting the Current Region option.

Sub CopyCurrentRegion()

Range(“A1”).CurrentRegion.Copy Sheets(“Sheet2”).Range(“A1”)

End Sub

On the CD

A workbook that contains this macro is available on the companion CD-ROM. The file is named range copy.xlsm.

889

Part VI: Programming Excel with VBA

Selecting to the end of a row or column

You probably are in the habit of using key combinations, such as pressing Ctrl+Shift+→ and Ctrl+Shift+↓, to select from the active cell to the end of a row or column. When you record these actions in Excel (using relative recording mode), you’ll find that the resulting code works as you would expect it to.

The following VBA procedure selects the range that begins at the active cell and extends down to the last cell in the column (or to the first empty cell, whichever comes first). When the range is selected, you can do whatever you want with it — copy it, move it, format it, and so on.

Sub SelectDown()

Range(ActiveCell, ActiveCell.End(xlDown)).Select

End Sub

Notice that the Range property has two arguments. These arguments represent the upper-left and lower-right cells in a range.

This example uses the End method of the Range object, which returns a Range object. The End method takes one argument, which can be any of the following constants: xlUp, xlDown, xlToLeft, or xlToRight.

On the CD

A workbook that contains this macro is available on the companion CD-ROM. The file is named select cells.xlsm.

Selecting a row or column

The macro that follows demonstrates how to select the column of the active cell. It uses the EntireColumn property, which returns a range that consists of a column:

Sub SelectColumn()

ActiveCell.EntireColumn.Select

End Sub

As you may suspect, an EntireRow property also is available, which returns a range that consists of a row.

If you want to perform an operation on all cells in the selected column, you don’t need to select the column. For example, when the following procedure is executed, all cells in the row that contains the active cell are made bold:

890

Chapter 44: VBA Examples

Sub MakeRowBold()

ActiveCell.EntireRow.Font.Bold = True

End Sub

Moving a range

Moving a range consists of cutting it to the Clipboard and then pasting it to another area. If you record your actions while performing a move operation, the macro recorder generates code as follows:

Sub MoveRange()

Range(“A1:C6”).Select

Selection.Cut

Range(“A10”).Select

ActiveSheet.Paste

End Sub

As demonstrated with copying earlier in this chapter (see “Copying a range”), this method is not the most efficient way to move a range of cells. In fact, you can do it with a single VBA statement, as follows:

Sub MoveRange2()

Range(“A1:C6”).Cut Range(“A10”)

End Sub

This statement takes advantage of the fact that the Cut method can use an argument that specifies the destination.

On the CD

A workbook that contains this macro is available on the companion CD-ROM. The file is named range move.xlsm.

Looping through a range efficiently

Many macros perform an operation on each cell in a range, or they may perform selective actions based on the content of each cell. These operations usually involve a For-Next loop that processes each cell in the range.

The following example demonstrates how to loop through all the cells in a range. In this case, the range is the current selection. In this example, Cell is a variable name that refers to the cell being processed. (Notice that this variable is declared as a Range object.) Within the For-Next loop, the single statement evaluates the cell. If the cell is negative, it’s converted to a positive value.

891

Part VI: Programming Excel with VBA

Sub ProcessCells()

Dim Cell As Range

For Each Cell In Selection

If Cell.Value < 0 Then Cell.Value = Cell.Value * -1

Next Cell

End Sub

The preceding example works, but what if the selection consists of an entire column or an entire range? This is not uncommon because Excel lets you perform operations on entire columns or rows. In this case, though, the macro seems to take forever because it loops through each cell — even those that are blank. What’s needed is a way to process only the nonblank cells.

You can accomplish this task by using the SelectSpecial method. In the following example, the SelectSpecial method is used to create a new object: the subset of the selection that consists of cells with constants (as opposed to formulas). This subset is processed, with the net effect of skipping all blank cells and all formula cells.

Sub ProcessCells2()

Dim ConstantCells As Range

Dim Cell As Range

‘Ignore errors

On Error Resume Next

‘Process the constants

Set ConstantCells = Selection.SpecialCells _

(xlConstants, xlNumbers)

For Each Cell In ConstantCells

If Cell.Value < 0 Then Cell.Value = Cell.Value * -1 Next Cell

End Sub

The ProcessCells2 procedure works fast, regardless of what is selected. For example, you can select the range, select all columns in the range, select all rows in the range, or even select the entire worksheet. In all these cases, only the cells that contain constants are processed inside the loop. This procedure is a vast improvement over the ProcessCells procedure presented earlier in this section.

Notice that the following statement is used in the procedure:

On Error Resume Next

This statement causes Excel to ignore any errors that occur and simply to process the next statement. This statement is necessary because the SpecialCells method produces an error if no cells qualify and because the numerical comparison will fail if a cell contains an error value.

Normal error checking is resumed when the procedure ends. To tell Excel explicitly to return to normal error-checking mode, use the following statement:

On Error GoTo 0

892

Chapter 44: VBA Examples

On the CD

This macro is available on the companion CD-ROM. The file is named skip blanks while looping.xlsm.

Prompting for a cell value

As discussed in Chapter 41, you can take advantage of the VBA InputBox function to ask the user to enter a value. Figure 44.2 shows an example.

FIGURE 44.2

Using the VBA InputBox function to get a value from the user.

You can assign this value to a variable and use it in your procedure. Often, however, you want to place the value into a cell. The following procedure demonstrates how to ask the user for a value and place it into cell A1 of the active worksheet, using only one statement:

Sub GetValue()

Range(“A1”).Value = InputBox(“Enter the value for cell A1”)

End Sub

Determining the type of selection

If your macro is designed to work with a range selection, you need to determine that a range is actually selected. Otherwise, the macro most likely fails. The following procedure identifies the type of object selected:

Sub SelectionType()

MsgBox TypeName(Selection)

End Sub

893

Part VI: Programming Excel with VBA

On the CD

A workbook that contains this macro is available on the companion CD-ROM. The file is named selection type.xlsm.

If a cell or a range is selected, the MsgBox displays Range. If your macro is designed to work only with ranges, you can use an If statement to ensure that a range is actually selected. The following is an example that displays a message and exits the procedure if the current selection is not a Range object:

Sub CheckSelection()

If TypeName(Selection) <> “Range” Then MsgBox “Select a range.”

Exit Sub End If

‘ ... [Other statements go here] End Sub

Another way to approach this task is to define a custom function that returns TRUE if the selection (represented by the sel argument) is a Range object, and FALSE otherwise. The following function does just that:

Function IsRange(sel) As Boolean

IsRange = False

If TypeName(sel) = “Range” Then IsRange = True

End Function

Here’s a more compact version of the IsRange function:

Function IsRange(sel) As Boolean

IsRange = (TypeName(sel) = “Range”)

End Function

If you enter the IsRange function in your module, you can rewrite the CheckSelection procedure as follows:

Sub CheckSelection()

If IsRange(Selection) Then

‘ ... [Other statements go here] Else

MsgBox “Select a range.” Exit Sub

End If

End Sub

894

Chapter 44: VBA Examples

Identifying a multiple selection

Excel enables you to make a multiple selection by pressing Ctrl while you select objects or ranges. This method can cause problems with some macros; for example, you can’t copy a multiple selection that consists of nonadjacent ranges. The following macro demonstrates how to determine whether the user has made a multiple selection:

Sub MultipleSelection()

If Selection.Areas.Count > 1 Then

MsgBox “Multiple selections not allowed.” Exit Sub

End If

‘ ... [Other statements go here] End Sub

This example uses the Areas method, which returns a collection of all Range objects in the selection. The Count property returns the number of objects that are in the collection.

The following is a VBA function that returns TRUE if the selection is a multiple selection:

Function IsMultiple(sel) As Boolean

IsMultiple = Selection.Areas.Count > 1

End Function

Counting selected cells

You can create a macro that works with the selected range of cells. Use the Count property of the Range object to determine how many cells are contained in a range selection (or any range, for that matter). For example, the following statement displays a message box that contains the number of cells in the current selection:

MsgBox Selection.Count

Caution

With the larger worksheet size introduced in Excel 2007, the Count property can generate an error. The Count property uses the Long data type, so the largest value that it can store is 2,147,483,647. For example, if the user selects 2,048 complete columns (2,147,483,648 cells), the Count property generates an error. Fortunately, Microsoft added a new property (CountLarge) that uses the Double data type, which can handle values up to 1.79+E^308.

For more on VBA data types, see upcoming Table 44.1.

Bottom line? In the vast majority of situations, the Count property will work fine. If there’s a chance that you may need to count more cells (such as all cells in a worksheet), use CountLarge instead of Count.

895