Microsoft Office Tutorials and References
In Depth Information
Working with Range Objects
Working with Range Objects
Much of the work that you will do in VBA involves cells and ranges in worksheets. The earlier
discussion on relative versus absolute macro recording (see “Relative or absolute recording?”)
exposes you to working with cells in VBA, but you need to know a lot more.
A Range object is contained in a Worksheet object and consists of a single cell or range of cells
on a single worksheet. In the sections that follow, I discuss three ways of referring to Range
objects in your VBA code:
h The Range property of a Worksheet or Range class object
h The Cells property of a Worksheet object
h The Offset property of a Range object
The Range property
The Range property returns a Range object. If you consult the Help system for the Range
property, you learn that this property has two syntaxes:
object.Range(cell1, cell2)
The Range property applies to two types of objects: a Worksheet object or a Range object.
Here, cell1 and cell2 refer to placeholders for terms that Excel recognizes as identifying the
range (in the first instance) and delineating the range (in the second instance). Following are a
few examples of using the Range property.
You’ve already seen examples like the following one earlier in the chapter. The instruction that
follows simply enters a value into the specified cell. In this case, it puts the value 12.3 into cell
A1 on Sheet1 of the active workbook:
Worksheets(“Sheet1”).Range(“A1”).Value = 12.3
The Range property also recognizes defined names in workbooks. Therefore, if a cell is named
Input , you can use the following statement to enter a value into that named cell:
Worksheets(“Sheet1”).Range(“Input”).Value = 100
The example that follows enters the same value into a range of 20 cells on the active sheet. If the
active sheet isn’t a worksheet, the statement causes an error message:
ActiveSheet.Range(“A1:B10”).Value = 2
Search JabSto ::

Custom Search