Page 158 - Excel Progamming Weekend Crash Course

h540629 ch10.qxd 9/2/03 9:34 AM Page 133

Session 10 — Using Ranges and Selections 133

Suppose you want cell B6 to display the sum of cells B1:B5. The following code uses a
named range to put the required formula in cell B6:
Dim r As Range
Set r = Range(“B6”)
r.Name = “Total”
Range(“Total”).Value = “=Sum(B1:B5)”

Now, the cell B6 contains the formula =Sum(B1:B5).
The syntax for the Names.Add method is given using named arguments to
remind you of this option. Remember, you can use named arguments with
Note any Excel method, or you can identify arguments by position as has been
done in many other examples in this book.
Range names that are assigned in this manner exist only as long as the code is running.
In other words, they are not saved with the workbook. When you want to define named
ranges that are persistent and should remain in effect the next time the workbook is opened,
you must explicitly add them to the workbook’s Names collection using its Add method:

Names.Add Name:=name, RefersTo:=reference
name is the name for the range and must be unique within the workbook, and reference
identifies the cells that name refers to. This argument is a string containing the following parts:
1. An equal sign.
2. The name of the worksheet containing the cells.
3. An exclamation point.
4. The column and row of the top left cell in the range.
5. Optional, for multicell ranges: a colon followed by the column and row of the lower
right cell.
When creating the cell references, you need to be aware of the difference between rela-
tive and absolute references. (These are covered in more detail in Session 11.)

An absolute reference refers to a specific location in the worksheet, as identified by
the row number and column letter. To create an absolute reference, include dollar
signs in the reference. For example, “$B$6” always refers to cell B6.
A relative reference refers to a location relative to the current active cell, in the
same way that a relative range is defined. To create a relative reference, omit the
dollar signs. For example, “B6” refers to the cell five rows down and one column to
the right of the active cell.

The following are examples of creating persistent named ranges in a workbook. This code
creates a named range “Sales Data” that refers to the cells B4:F10 in sheet1:

ActiveWorkbook.Names.Add Name:=”Sales Data”,
RefersTo:=”=sheet1!$B$4:$F$10”)

This code creates a named range that refers to the single cell that is one row below the
active cell in the active workbook: