Page 204 - Microsoft Office Excel 2003 Programming Inside Out

Part 3: The Excel Object Model
Microsoft Office Excel 2003 Programming Inside Out

can use a macro to do the work for you. The following CreateNames routine will build a
named range for each of the time periods on the current worksheet and name it based on the
name of the worksheet and the time period. The basic syntax is expression.Add(Name,
RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal,
RefersToR1C1, RefersToR1C1Local).
Of the various parameters that are passed to the method, only four of them are of importance.
● Name A variant value that specifies the name that is to be given to the range.
● RefersTo A variant value that specifies the cells to be included in the range using A1
notation. Required if RefersToR1C1 is not used.
● RefersToR1C1 A variant value that specifies the cells to be included in the range
using R1C1-style notation. Required if RefersTo is not used.
Chapter 8
● Visible An optional variant value that determines whether the Name is visible or not.
Setting the property to True (the default) means the Name will appear in the Define
Name, Paste Name, and Goto dialog boxes, while setting the property to False means
the Name won’t appear in those three dialog boxes (although you may still refer to the
Name in formulas and VBA code).

Inside Out

Naming Ranges
There are a few guidelines to follow when naming a range:

● A name must start with a letter or an underscore (_) character. The rest of the name
can contain any combination of letters, digits, periods (.), or underscores.
● A name cannot be the same as an existing cell reference (B22, CB76, R2C20, and
so on).
● A name cannot contain spaces or other special characters, curly braces, square
brackets, or parentheses.
● A name cannot exceed 255 characters. Also, names over 253 characters are not
selectable from the drop-down list.
● Names are not case sensitive. MyRange is the same as myrange.
● A name must be unique to workbook; you can’t use the same name to refer to ranges
on different worksheets.

The following sample macro, CreateNames, works by looping through each of the columns
that contains entries based on time. On each pass of the loop, the name of the current work-
sheet and the label of the column being marked are used to generate the name of the range
in the format MonthHourAMPM, as shown in Figure 8-6. (The 1:00 P.M. entries on the
February worksheet would be named as February1PM.)

178