Defined Names
This page describes how to use Defined Names in your workbooks.
A Defined Name is a text descriptor that you can use to describe the meaning of or content of a cell, a range of cells,
a constant, or a formula. Once the Defined Name is established, you can use that Name wherever you would ordinarily use
the actual cell address or other content. This makes the formulas much easier to understand and maintain. For example, the formula
=G12*K15
isn't very meaningful unless you happen to know the meaning of cells G12 and K15.
However, if you assign Defined Names to G12 and K15, you can
write your formula similar to the following:
=Income*TaxRate
Clearly, using the Defined Names makes the formula much easier to understand and maintain.
There are two ways of creating a Defined Name (not including doing so with VBA
code). The first method is to select the cell or range of cells to which you
want to assign a Name. Then, click in the Name Box and type the Name. The Name Box is the white
rectangle to the left of the "A" column heading and above the "1" row heading. If you type in the Name Box a Name that is already defined,
Excel will display the range referenced by that Name. It will not overwrite and change the existing Defined Name. The second way to create a Name
is to select the cell or range of cells to which you want to assign a Name, go to the Insert menu, choose Name, then
Define. This will display the Defined Names dialog box. There, type in the Name you want to use. In that dialog, you can also
change the cell(s) to which an existing Name refers.
A Defined Name must begin with a letter or an underscore ( _ ) and consist of only letters, numbers, or underscores. Spaces are not permitted in
a Defined Name. Moreover, a Defined Name may not be the same as a valid cell reference. For example, the name
AB11 is invalid because AB11 is a valid cell reference. Names are not
case sensitive.
|
CAUTION: If you are working on a workbook that will be used in both Excel 2003 and Excel 2007, be aware that many
Names that are valid in Excel 2003 are invalid in Excel 2007. This is due to the increased number of columns in Excel 2007.
For example, in Excel 2003, the Name ABC1 is valid because it is not a cell reference. However,
in Excel 2007, ABC1 is, in fact, a valid cell reference, so the Name is invalid.
|
The user interface for working with Defined Names in Excel 2003 and earlier is relatively primitive. Jan Karel Pieterse has created a nice
add-in, available for free at www.jkp-ads.com/officemarketplacenm-en.asp, that
greatly improves on the basic Defined Name manager. The Defined Name Manager in Excel 2007 and later has been greatly improved over
previous versions.
A Defined Name is not limited to referencing a cell or range of cells. A Name can refer to a constant, either a numerical value or
a text value. It can also refer to a formula. For example, you could have a Defined Name of MaxRows with a value of 25, and then use
that name anywhere you would otherwise use that number. Using a Name has the advantage that if you use it in multiple locations and later
need to change the value, you need change it only in the definition and the new value will be used in all other locations.
When you use a Define Name in a formula or cell, you don't include parentheses as you would with a function. For example, if you have
a Name defined as MyName with a value of Chip Pearson, you would enter in the
cells =MyName, not =MyName(). Using the parentheses would cause
a #REF error.
A Defined Name may also contain a formula. For example, you can define a Name named TheSum and
assign the formula =SUM($A$1:$A$100) to the Name. Then, you can use =TheSum
anywhere you want to get the sum of A1:A100. As noted before, you do not use parentheses in the reference to
the name. =TheSum() will cause a #VALUE error. You can use any formula you want
in a Name, but you cannot pass parameters to the formula defined by the Name.
If you use a formula in a Defined Name, that formula is evaluated as if it were an
array formula. There is no way to force a formula in a Defined Name to be
evaluated as a non-array formula.
Typically, a Defined Name has global scope. This means that the Name may be defined on any one worksheet and then referenced on any worksheet
in the workbook. For example, if you have a cell, say A1 on Sheet1, that
contains the last printed date, and you name that cell LastPrintDate, you can use the
Name
LastPrintDate on any worksheet and it will always refer back to A1 on
Sheet1.
It is possible, though, to have Names that are scoped to only a single worksheet, and any or all of the worksheets may have a sheet scoped
names with the same name. When that Name is used on a worksheet, the value of the Name on the worksheet that is using it is evaluated.
If you have several sheet scoped names, on different worksheets, those names need not refer to the same cell reference. That is, the name
SheetLastPrinted on Sheet1 may refer to A1
on Sheet1, and the name SheetLastPrinted
on Sheet2 can refer to K10 on Sheet2.
When the name SheetLastPrinted is used on Sheet1, the value
is taken from A1 on Sheet1. If the
Name is used on
Sheet2, the value is taken from K10 on Sheet2.
To create sheet scoped Names, select the cell to be named, open the Defined Name Dialog (Insert menu, Name, then
Define), and enter the name as Sheet1!TheName (where Sheet1 is the
name of the sheet on which the Name should be scoped. The presences of the sheet name causes Excel
to make the name sheet scoped. Repeat this process on all worksheets that need to use the sheet scoped name. When defining the Names, enclose
the sheet name within apostrophes if the sheet name contains spaces or other non-numeric characters.
E.g.,
'Sheet One'!TheName.
One of the very useful features of Defined Names is to circumvent some restrictions of Conditional Formatting and Data Validation.
In Condition Formatting, if you use the Formula Is method, all cells in the formula must be on the same worksheet as the
cell to which Conditional Formatting is applied. For example, if you are applying Conditional Formatting to cell
A1 on Sheet1, you will not be able to use a formula like
=Sheet2!A10>100, because the formula refers to a cell on another sheet. You can get around this
limitaiton by assigning a Name to the cell on the other sheet -- e.g., assign the name TestVal to
Sheet2!A10 -- and then use the defined name in the Conditional Formatting dialog:
=TestVal>10.
This same method can be used for the values list in a List type Data Validation constraint. If your list of valid values is in the
range Sheet2!A1:A10, you cannot reference that range as the source of the validation list if the cell being
validated is on a different worksheet. However, if you assign a Defined Name to that range, e.g.,
ValidList, you can use =ValidList as the values list for Data Validation.
In Excel 2003 and earlier, the defined name box cannot be resized, and it truncates the display of names at about 16 characters. So,
if you have two long names like SomeLongDefinedNameOne and SomeLongDefinedNameTwo,
you cannot tell which is which in the Name Box dropdown. However, using a bit of code, you can expand the width of the drop down list. This
code will not widen the name box itself, as it appears on the formula bar, but widens the drop down list. The code to do this
is shown below.
Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" _
(ByVal hWnd1 As Long, ByVal hWnd2 As Long, _
ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
(ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, lParam As Any) As Long
Sub WidenNameBoxDrop2()
Dim Res As Long
Const CB_SETDROPPEDWIDTH = 352
Const cWidth = 400 '< Change To Desire Width in Pixels
Res = SendMessage( _
FindWindowEx( _
FindWindowEx( _
FindWindow("XLMAIN", Application.Caption) _
, 0, "EXCEL;", vbNullString) _
, 0, "combobox", vbNullString), _
CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub
Put all of the code shown above in some code module, and then in your Auto_Open procedure or in the
Workbook_Open procedure call the WidenNameBoxDrop2 procedure. For example,
Sub Auto_Open()
WidenNameBoxDrop2
End Sub
In Excel 2007 and later, the Name Box is sizable, so the code given above in not necessary.
You can apply Names to existing formulas. If your formula uses regular cell references and you later assign Names to those cells, you
can automatically update the formula to use the Defined Names. For example, suppose you have the formula
=A5+D5. If you later assign the Name CellOne to A5
and CellTwo to D5, you can select the cell with the formula, go to the
Insert menu, choose Name, and then Apply. Choose the appropriate names in the list, or simply select them
all, and click OK. Excel will alter the formula to =CellOne+CellTwo.
You can use VBA code to work with defined names. To add a name, use
ThisWorkbook.Names.Add Name:="SomeName", _
RefersTo:=Worksheets("Sheet2").Range("A1:A10")
This will assign the Name SomeName to the range A1:A10 on
Sheet2. If you add a Name that already exists, the Name is automatically deleted and recreated with the
new reference. It is not necessary to delete the Name before recreating it.
By default, names created manually or with VBA code are visible -- they will appear in the Name Box drop down and in the Names dialog.
You can, however, make the name hidden so that it isn't visible to the user. A hidden Name can be used in any manner just like a normal,
visible Name. To make a name hidden, you set the Visible parameter to False. For example,
ThisWorkbook.Names.Add Name:="SomeName2", _
RefersTo:=Worksheets("Sheet2").Range("B1:B10"), _
Visible:=False
The name SomeName2 can be used exactly as a visible name, but will not appear to the user. The only way to
hide a Defined Name is via VBA code. There is nothing in the Excel user interface that allows you to hide a Name.
To delete a name using VBA code, use code like the following:
ThisWorkbook.Names("TheName").Delete
Using Defined Names in VBA code is different than using them in worksheet cells. You must get the value of the Name using the
RefersTo or RefersToRange properties. If a Name doesn't refer to a cell or
range of cells (e.g., it refers to a constant), an attempt to use RefersToRange will fail. To get the
value of a Name that contains a constant, use the RefersTo property.
V = ThisWorkbook.Names("TheName").RefersTo
If the name refers to a range of 2 or more cells, you can assign it to a Range type variable. For example,
Dim R As Range
Set R = ThisWorkbook.Names("BigName").RefersToRange
Debug.Print R.Address
If the name refers to a constant, you'll need to strip out some characters to get the actual value. For example, if the
name MyName refers to the text constant Chip Pearson, the
RefersTo property will return the text ="Chip Pearson". You need to
strip out the leading equals sign and the enclosing quotes. You can do this with code like the following:
Dim S As String
S = ThisWorkbook.Names("MyName").RefersTo
S = Mid(S, 3, Len(S) - 3)
Debug.Print S
If the name refers to a numeric constant, there will be a leading equal sign but no quotes. Thus, you'll need to strip off
the leading equals sign. The code below illustrates this.
Dim S As String
S = ThisWorkbook.Names("MaxPages").RefersTo
S = Mid(S, 2)
Debug.Print S
We can put all this together into a function that will return what the given name refers to, be it a range, text constant, or
numeric constant.
Function GetNameRefersTo(TheName As String) As String
Dim S As String
Dim HasRef As Boolean
Dim R As Range
Dim NM As Name
Set NM = ThisWorkbook.Names(TheName)
On Error Resume Next
Set R = NM.RefersToRange
If Err.Number = 0 Then
HasRef = True
Else
HasRef = False
End If
If HasRef = True Then
S = R.Text
Else
S = NM.RefersTo
If StrComp(Mid(S, 2, 1), Chr(34), vbBinaryCompare) = 0 Then
' text constant
S = Mid(S, 3, Len(S) - 3)
Else
' numeric contant
S = Mid(S, 2)
End If
End If
GetNameRefersTo = S
End Function
|
This page last updated: 6-June-2009. |