Using Define Name

The first character of a name must be a letter. Subsequent characters can be letters or numbers or the period or underline character. Spaces are not allowed (the space character is the intersection operator) Excel will substitute an underline character for a space in any name that it proposes based on text in worksheet cells. 

To indicate breaks between words in a name, use a period or imderline character, or use capitalization. 

Names that look like references (e.g., A1), will not be accepted by Define Name. Since Excel can also use the RlCl reference style, the letters R and C cannot be used as names. 

Instead of a reference, you can type a numeric value or a formula in the Refers To box. You can use named formulas to simplify long worksheet formulas by assigning names to parts of the formula. See Returning an Array of Unique Entries in a List in Chapter 4 or A Drop-down List Box on a Worksheet in Chapter 8 for examples of this technique. 

---

You can also define a name by using the Name box (the cell reference area in the formula bar). Simply select the cell or range on the sheet (the range will be displayed in the Name box), click the cursor in the Name box (the typing area will be highlighted), type the name, then press ENTER. (Excel does not propose a name based on a text label above or to the left of the selected range, as it does when you use Define Name or Create Names you have to type in the name yourself.)... 

Use Apply Names (Figure 3-10) if you have created a spreadsheet with formulas using cell references and now want to replace the cell references with names. First, use Define Name or Create Names to assign names to the references. Then choose Apply Names. The names that you have assigned will be shown in the list box. The Ignore Relative/Absolute box should usually be checked. Select a name from the list and press OK. All cells containing the reference will be replaced by the name. 

The Label... command was introduced in Excel 97. It also allows you to use names (except that they re called labels) in formulas. It is intended primarily to label rows and column headings of tables. The Label... command doesn t provide any features that are not available by using Define Name or Create Name, and in fact is much less versatile than using names. 

Use Define Name to change the global name to a local name in the active sheet as described in the preceding box. 

Let s create the formula in a stepwise fashion. To simplify the formulas, we ll use Define Name to create named formulas (see "Using Create Names" in Chapter 3). These formulas will be located in the Define Name dialog box, not in worksheet cells. 

This formula returns the number of items in the list that match the input string. These two formulas are combined in the formula =OFFSET( A 1,SubListStart,0,SubListLength,1) to return a reference to the cells containing the sublist. This formula was entered as a named formula (using Define Name) and given the name DropList. In fact, all the formulas in this example can be entered as named formulas. 

In an Excel worksheet, you have to use Define Name or Create Name to assign a name to a variable, but variable names in VBA are automatically assigned as you type the VBA code in a module. There are just a few rules for naming variables or arguments ... 

---