power4XL Microsoft Excel Resource Center
Free Macros, Formulas, Functions, Tutorials, Downloads, Add-Ins & More!

Advanced range name techniques

ShareThis

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.

 

Prev | Contents | Next

Copyright notice:  This site and all content, including computer code and spreadsheet examples, are copyright 2006 by Fritz Dooley.  License is granted for individual users to download examples and to copy code directly into user's spreadsheets and Visual Basic for Applications files.  Users may not redistribute code in any way.  Providing hyperlinks to this web site is encouraged, but posting code and examples on other web sites is expressly forbidden.  "Microsoft" is a registered trademark of Microsoft Corporation.   Neither this web site nor Fritz Dooley is affiliated with Microsoft Corporation.