Microsoft Office Tutorials and References
In Depth Information
A single cell is a range as far as VBA is concerned, and ActiveCell is the object name in VBA of
the single active cell on the active worksheet. There is no such object as “ActiveRange,” but there are
many ways to identify particular ranges, one of the most common being the Selection object.
If you were to select any range of cells, and execute this line of code, all cells in that selection would
immediately contain the word “Hello”:
Selection.Value = “Hello”
You may be interested to know that named ranges are fair game for VBA to refer to and manipulate,
just like any other range. In fact there is a Names collection object for named ranges.
As an example, say you have previously named a range myRange . This line of code in a VBA macro
would place the word “Hello” in all cells in your named range:
Range(“myRange”).Value = “Hello”
As you have seen, you do not need to select your range in order to work with it. For most operations
on cells or ranges, you can refer to the range and its parent worksheet. The following line of code
can be executed from any worksheet in your workbook, as an example of establishing a bold format
for a range of cells on Sheet1.
Worksheets(“Sheet1”).Range(“A1:D25”).Font.Bold = True
There are times when you will want to refer to all the cells on a worksheet, instead of limiting your
operation to a particular range. For example, suppose as part of your macro you want to clear the
contents of every cell on the worksheet. Starting with version 2007, clearing the contents of the
entire grid of worksheet cells can be expressed as Range(“A1:XFD1048576”).ClearContents .
However, if the workbook is being used in a version of Excel prior to 2007, that same operation
could be expressed as Range(“A1:IV65536”).ClearContents . Fortunately, you can avoid errors
and confusion by using the Cells object as shown in the following example, which refers to all
worksheet cells in whichever version of Excel is being used at the moment:
You can do some useful operations using the Cells object when you want to involve the entire
worksheet. Suppose you have set up Sheet1 as a template, with formatted ranges, labels, values,
and formulas, and you want Sheet2 to be established the same way. This line of code will copy the
Sheet1 cells and paste them to Sheet2.
An interesting brand of range objects is Excel’s group of SpecialCells . If you have not yet examined
SpecialCells , press the F5 key to call the Go To dialog. Click the Special button and you will see
more than a dozen classifications of SpecialCells .