Microsoft Office 2000/Visual Basic Programmer's Guide

Using the Offset Property

You can use the Offset property to return a Range object with the same dimensions as a specified Range object but offset from the specified range. For example, you could use the Offset property to create a new Range object adjacent to the active cell to contain calculated values based on the active cell.

The Offset property is useful in circumstances where you do not know the specific address of the cells you will need to work with, but you do know where the cell is located in relation to other cells you need to work with. For example, you may have a command bar button in your custom solution that fills the active cell with the average of the values in the two cells immediately to the left of the active cell:

ActiveCell.Value = (ActiveCell.Offset(0, -2) + ActiveCell.Offset(0, -1)/2)

For an example that illustrates how to use the Offset property to create full names out of a column of first names and a column of last names, see the MergeNamesExample procedure in the modExcelSamples module in ExcelSamples.xls in the ODETools\V9\Samples\OPG\Samples\CH05 subfolder on the Office 2000 Developer CD-ROM.