Names

Descripton of Names

Entry of a Name

The quickest way to enter a name is in the name box at the far left of the formula bar.

Restrictions on Names

Let's get what you can and can't do with names in the name box clear, Posting by Norman Harker, 2003-01-27, misc.

• If no name exists for a cell or range you may type the name in the name box rather than use insert name define.
• If the cell or range already has a name you may type a new name in the name box and it will be accepted as a name for that cell or range as long as it is not a name that is currently used elsewhere. However, the first name given will still exist and will still be the one shown in the name box when you select the cell or range. In formulas etc.  You will still be able to use both the new name and the old name. This will remain the case until you use Insert -> Name -> Define, select the old name and delete it.
• It's not good technique to have more than one name for the same cell or range. If you stuff up with spelling a name or decide you want to be more explicit or decide to change the cell a name refers to you are better off using Insert > Name Define as the method of executing changes.
• A name cannot be assigned to a name that could be confused for a cell address   (A1,...,IV65536). 

Syntax of a name:

• no spaces
• underscore is the only allowable character in addition to letters (A-Z), digits(0-9)
• must start with an alpha character
• can't look like cell references
• max length 255
• can't be names of functions (excel will cope if they are the names of built-in functions but it can't handle the names being those of functions in the analysis pak or UDF's - thanks to Norman Harker for this rule :) )

A name is applicable for the entire workbook, unless explicitly given a sheetname. [..find a specific reference and usage...]

Macros involving named ranges (#names)

Macro to Show Names

Sub ShowAlllNames()
'Bob Umlas, excel.misc,  2007-05-03
Dim nName As Name
    For Each nName In Names
            nName.Visible=true
    Next nName
End Sub

Macro to Delete all names in a workbook

 Sub DeleteallNames()
 'Mike, excel.misc, 2007-03-14
 Dim nName As Name
     For Each nName In Names
             nName.Delete
     Next nName
 End Sub

Related

• John Green has a Chapter on Names in his book "Excel 2000 VBA", Programmer's Reference, Chapt 6 in 2000, Chapter xx in 2002. (see Stephen Bullen's site)
• xlDynamic.com : Names In Excel, Bob Phillips

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved