|
Range reference functions in VBA offer a great deal of flexibility beyond
reading and writing to single cells. VBA allows you to resize or shift
cell range name references on the fly. Awareness of this functionality up
front can save you time during your design process, since you won't need
to change the sizes or references of your range names constantly within your
worksheet. This functionality also allows flexibility in changing
references on the fly during code execution as may be needed by your
application. For instance, you may need to change the size of a range of
output based on number of iterations run.
We'll look at two specific range name manipulation methods here - the .Resize
property and the .Offset property.
For this illustration, we'll use a worksheet that has range names defined as in
the highlighted cells in the two screenshots below. Notice that MyRange1
(B3) is a subset of MyRange2 (B3:B10).
Now for the VBA code. Note that in most of the examples below, SomeValue
refers to an array rather than a single variable. If you're not skilled in
the use of arrays, hang in there - that's coming next.
The .Resize property allows you to resize a range reference on the fly in VBA
code. Thus, the following two VBA statements are equivalent:
The two arguments to the .Resize property refer to rows and columns.
Note that the VBA code does not change the actual range name references within
the worksheet; the range reference change only applies to the line of code in
which it appears. Thus, after executing the code above, MyRange1 still
refers to the singe cell B3 in the worksheet. Because VBA offers such
flexibility in resizing ranges on the fly, we often recommend defining range
names to refer to a single cell in the worksheet, then resizing it as needed
with VBA. This helps avoid introduction of errors which can occur when
rows or columns are added to worksheets, or where ranges need to be resized in
the worksheet for other reasons.
The .Offset property allows you to adjust a range reference a specified distance
from the original reference, maintaining the same range size and dimensions.
Thus, the following two statements are equivalent, given the range references
highlighted in the above screenshots:
Finally, the .Resize and .Offset properties can be combined in a single statement.
Thus, all of the following statements are equivalent:
Note that the reference is always with respect to the upper left cell in the
range. Also, it does not matter in which order you use .Offset and .Range
within the same statement.
| 
