|
As illustrated in the previous example, passing a single variable to and from a
worksheet is easily achieved. To recap, the following worksheet contains
the range name "MyRange1" for cell B3:
The following two functions are equivalent - in other words, the cell can be
referenced by its range name OR using A1 notation:
We strongly recommend using named ranges
wherever values are being passed to and from spreadsheets. There are two
primary benefits of this practice:
-
Often in the building of a model,
rows and columns are inserted or deleted, or cell ranges are moved to new
locations. While Excel automatically updates references to that range
within the workbook, VBA references to the range are not updated. This
can have unintended - and sometimes catastrophic - consequences. For
example, your VBA code may overwrite cells containing formulas or other
important information if the A1 range reference is incorrect.
-
Also, using logical names for
worksheet ranges makes it easy to read the VBA code. "Iterations =
Range("Iterations")" is much easier to make sense of than "Iterations =
Range("$B$23")". This readability also helps in debugging your code.
Now we'll look at some advanced uses of range names.
| 
