Microsoft Office Tutorials and References
In Depth Information
Charts, of course, use data stored in ranges. Therefore, it’s important that you
understand how to work with ranges using VBA. The information in this section is intended
to be practical rather than comprehensive. If you want more details, consult Excel’s
Referencing a range
VBA code can reference a range in a number of different ways:
The Range property
The Cells property
The Offset property
THE RANGE PROPERTY
You can use the Range property to refer to a range directly, by using a cell address
or name. The following example assigns the value in cell A1 to a variable named
Init . In this case, the statement accesses the Range object’s Value property.
Init = Range(“A1”).Value
In addition to the Value property, VBA enables you to access a number of other
properties of a Range object. For example, the following statement counts the
number of cells in a range and assigns the value to the Cnt variable.
Cnt = Range(“A1:C300”).Count
THE CELLS PROPERTY
Another way to reference a range is to use the Cells property. The Cells property
accepts two arguments (a row number and a column number), and returns a single
cell. The following statement assigns the value in cell A1 to a variable named
FirstCell = Cells(1, 1).Value
THE OFFSET PROPERTY
The Offset property (like the Range and Cells properties) also returns a Range object.
The Offset property is used in conjunction with a range. It takes two arguments that
correspond to the relative position from the upper-left cell of the specified Range
object. The arguments can be positive (down or right), negative (up or left), or zero.