| Microsoft Office 2000/Visual Basic Programmer's Guide | |
The ActiveCell property returns a Range object representing the currently active cell. When a single cell is selected, the ActiveCell property returns a Range object representing that single cell. When multiple cells are selected, the ActiveCell property represents the single active cell within the current selection. When a cell or group of cells is selected, the Selection property returns a Range object representing all the cells within the current selection.
To understand how the ActiveCell and Selection properties relate to one another, consider the case where a user selects cells A1 through F1 by clicking cell A1 and dragging until the selection extends over cell F1. In this case, the ActiveCell property returns a Range object that represents cell A1. The Selection property returns a Range object representing cells A1 through F1.
When you work with Excel's user interface, you typically select a cell or group of cells and then perform some action on the selected cell or cells, such as entering a value for a single cell or formatting a group of cells. When you use VBA to work with cells, you don't need to make a selection before performing some action on a cell or group of cells. Instead, you need only return a Range object representing the cell or cells you want to work with. For example, to enter "January" as the value for cell A1 by using the user interface, you would select cell A1 and type January. The following sample performs the same action in VBA:
ActiveSheet.Range("A1").Value = "January"
Using VBA to work with a Range object in this manner does not change the selected cells on the current worksheet. However, you can make your VBA code act upon cells in the same way as a user working through the user interface by using the Range object's Select method to select a cell or range of cells and then using the Range object's Activate method to activate a cell within the current selection. For example, the following code selects cells A1 through A6 and then makes cell A3 the active cell:
With ActiveSheet
.Range("A1:A6").Select
.Range("A3").Activate
End With
When you use the Select method to select multiple cells, the first cell referenced will be the active cell. For example, in the preceding sample, after the Select method is executed, the ActiveCell property returns a reference to cell A1, even though cells A1 through A6 are selected. After the Activate method is executed in the next line of code, the ActiveCell property returns a reference to cell A3 while cells A1 through A6 remain selected. The next example illustrates how to return a Range object by using the ActiveCell property or the Selection property:
Dim rngActiveRange As Excel.Range
' Range object returned from the Selection property.
Set rngActiveRange = Selection
Call PrintRangeInfo(rngActiveRange)
' Range object returned from the ActiveCell property.
Set rngActiveRange = ActiveCell
Call PrintRangeInfo(rngActiveRange)
The PrintRangeInfo custom procedure called in the preceding example prints information about the cell or cells contained in the Range object passed in the argument to the procedure. This procedure is available in the modExcelSamples module in ExcelSamples.xls in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.
When you are learning to work with the Excel object model, it is often helpful to turn on the macro recorder and carry out the steps you want to accomplish and then examine the VBA code that results to see which objects, properties, and methods are used. You should be aware, however, that in many cases the macro recorder records your actions from the perspective of a user interacting with the user interface. This means that the Selection object, the Select method, and the Activate method are used over and over.
Once you get a solid grasp on the most efficient way to work with Excel objects, you will find yourself rewriting or restructuring the VBA code written by the macro recorder to use the Range object instead.