This article applies to all versions of Microsoft Excel For Windows®, unless stated otherwise.

Author(s): Bob Phillips
© Copyright, 2004, Bob Phillips

Date Written: 28th September 2004
Last Updated: 28th September 2004

Article Summary:

Names in Excel are a powerful facility, that are useful in the management of the spreadsheet, make formulae more readable, and in some instances are necessary to use certain functionality.

This paper discusses various aspects of Names in Excel, how to use them, where they can be used, and some of the nuances and tricks associated with them.

Article Contents:

This paper is structured in the following way:

Introduction
Advantages Of Using Names
Creating Names
Types of Names
Names in VBA
Duplicate Names in VBA
Miscellany
Acknowledgements and References

This article assumes familiarity with the following:

The Excel Application
Worksheet functions, such as LOOKUP, INDEX, MATCH, etc
Visual Basic For Applications (VBA) programming concepts (for the section Names In VBA)

Introduction

What Are Names?

Names are a powerful, but often under-utilised facility within Excel, one that is not readily understood by most Excel users. Those that do know of the facility are not always aware of the full functionality and power, beyond simple usage of Names.

Advantages Of Names

It may be over-stating it to say that Excel Names are absolutely necessary, but they are an extremely useful facility, one that can facilitate better spreadsheet development, and make the spreadsheet easier to maintain, and easier to read.

The section entitled Advantages Of Using Names gives some reasons as to why you should consider them, and a few where it may be absolutely necessary.

The previous section, Creating Names In Excel, covers the steps required to create a name in the first instance. As is typical of Excel, there is more than one way, which are all covered here.

Types Of Names

Although many Excel users will be aware of simple workbook Names, Excel support various flavours, or types of Names, which is covered in the section entitled Types of Names.

Advantages Of Using Names

Maintainability

Creating Names in a spreadsheet can be considered analogous to using variables in a program, such as a VBA procedure. The actual, underlying, value is stored and used, but is hidden from view. Should the underlying value change, a change only needs to be made where the value is assigned or defined, rather than the numerous places it is used. This makes the maintenance of the spreadsheet much simpler, and is less likely to lead to errors or omissions.

Readability

As we can see above, using Names helps the spreadsheet developer in creating and maintaining that spreadsheet. It can also make the spreadsheet more readable and easier to understand, as the Name can (should be) descriptive, describing what that value is and what it is used for, rather than just what the value is. The purpose of a Name such as VAT is far clearer than putting in a formula reference such as H10, where H10 has the value .175. This is equally true where the Name refers to a range.

Necessity

In some circumstances a Name is a necessity, the functionality cannot be achieved without it. For instance, if using 'Data Validation' with the list option, it can be useful to store the list in a worksheet range, and use that range reference in the 'Data Validation' list Source editbox. If the 'Data Validation' cell is on a different worksheet from the list range, you will get an error if you try to use a range reference, even if preceded by the worksheet name. The way to overcome this is to define a Name for that list range, and use that in the 'Data Validation' list Source editbox.

Similarly, when using 'Conditional Formatting', it is not possible to use a range reference on another worksheet in any formula defined, but again a range Name overcomes this problem.

Creating Names

To create a Name, go to the Insert>Name>Define... menu, and when the dialog box pops up, type the name value into the 'Names in workbook' box. Then either select the target range using the mouse, or enter the range reference directly into the 'RefersTo' edit box. There is no necessity to precede it with the worksheet name, unless you intend it to refer to a worksheet other than the active worksheet, as Excel will default.

Consideration should be given as to whether the range is to be an absolute range or a relative range, as its usage will vary dependent upon the choice.

You could also select the range prior to invoking the dialog box, and it will be pre-loaded as an absolute range reference in 'RefersTo' editbox.

A further way of creating a Name is to select the range, and then type the name value in the Names dialog box, the drop-down list to the left of the formula bar (see Fig. 1). It should be noted that a Name created in this way will refer to an absolute range.

Fig. 1

Names can be amended by again going to the Insert>Name>Define... menu, and when the dialog box pops up, select the name from the list. Click in the 'RefersTo' editbox, and hit F2 to enter edit-mode. The range can then easily be amended.

Types of Names

Names come in two flavours, what I call global and local Names. Global Names apply to the whole workbook, every worksheet within that workbook. Local Names only apply to the worksheet on which they are created.

Global Names

The most common type of Name in Excel is the global, or workbook, Name, which refers to a range within a specific worksheet within the workbook, or a constant value, but can be used in a formula with no qualification anywhere in the workbook.

It can even be used from another workbook, if preceded by the source workbook name.

In essence, wherever you can use a reference, you can use a global Name.

Local Names

Local, or worksheet, Names differ from global Names in that whilst it also refers to a range within a specific worksheet, or a constant value, when accessed from another worksheet, or workbook, the source worksheet name has to precede it in its usage. For example, to access the Name 'myRange' which was created as a local name local to 'Sheet2' from 'Sheet1', a formula such as =Sheet2!myRange would be used.

If the local Name is being accessed from the same worksheet, it is not necessary to add the sheet name qualifier.

When accessing a local Name from another workbook, it needs to be preceded by both the workbook name and the worksheet name, such as =[somebook.xls]Sheet1!myRange.

Multiple Names

One aspect of the functionality of local Names is that you could use the same Name to point to different ranges within the workbook, or different constant values. For instance, on Sheet1, you could create a local Name of RangeName which points at Sheet1!A1:A10. On Sheet2, you can create another local Name, also called RangeName which points at B2:F13. Either can be accessed from it's own worksheet without the worksheet name qualifier, or from an other worksheet as long the worksheet name qualifier is included.

From this, you should be able to see some possibilities of having the same Name, such as having a current and a previous worksheet, each with a local Name of VAT, with a value of 17.5% on the current, and say 15% on the previous.

It is even possible to have a global Name that applies to (most of) the workbook, but have a local Name on a specific worksheet where a different value is required. As mentioned previously, a local Name can be accessed from another worksheet as long as it is preceded by its local worksheet name. This is true even if there is a global and local Name. But, you cannot (in my understanding) access a global Name from the worksheet where there is a local Name with the same name. This may not be a problem, as the definition of a local name to that worksheet should mean that the global Name name is not required there, but it should be borne in mind.

Fig 2. and 3. show examples of the Names dialog box with a global (workbook) name, and with a local (worksheet) Name. Note that with the local Name, the sheet name that it is local to is shown at the right of the Names listbox.

Fig. 2

worksheet names

Fig. 3

Non-Range Names

Names can be used in another, very useful, way that is under-appreciated generally. I am referring to a Name that doesn't refer to a range in an Excel worksheet, but to a value, that is a non-range Name. In this way, it is possible to create constants that can be used in your formulae. As an example of their usefulness, consider the following two formulas

=A1*.175
=A1*VAT

These formulas are returning an amount of VAT for a price in cell A1, but which do you think is more readable? Consider it in a more complex formula, and you should be able to see the benefit of creating non-range names.

Non-range Names are created in the same way, in the Names dialog box with the name of VAT, but in the RefersTo box, a value of =.175, not a range reference.

Note. When using percentage values, it is not necessary to use values such as .175, in Excel you can input a value in the friendlier 17.5% format. This applies equally to direct entry in formulae as to the RefersTo field of the names dialog. In the definition of VAT that was previously used, we could better use =17.5% in the RefersTo box.

A further useful use of non-range names is to simplify some of the more complex formulae by creating a Name to return a formula result. As an example, consider the following formula

=IF(A1="A",SUMIF($A$10:$A$25,A1,$B$10:$B$25),IF(A1="B",SUMIF($A$10:$A$25,A1,$B$10:$B$25)*15%,IF(A1="C",SUMIF($A$10:$A$25,A1,$B$10:$B$25)*25%,SUMIF($A$10:$A$25,A1,$B$10:$B$25)*33%)))

This is unwieldy to say the least, and one way to improve this is to use non-range names. If we define a name of say ProductSum with a RefersTo value of =SUMIF($A$10:$A$25,A1,$B$10:$B$25), we can re-cut this formula as

=IF(A1="A",ProductSum,IF(A1="B",ProductSum*15%,IF(A1="C",ProductSum*25%,ProductSum*33%)))

This version is shorter, is simpler to read, and will undoubtedly be easier to debug. We could even extend it to create further Names for formulas that use this name, such as for =ProductSum*15%, =ProductSum*25%, etc.

One interesting point to note about non-range names is that they do not show in the Names box, that is in the drop-down list to the left of the formula bar (see Fig. 1., containing the value G1). They can be created and viewed via the Insert>Name>Define... menu option as can range names. This is a shame, for as well as providing a list of the workbook names, you can quickly create a range name by selecting the range and typing the name into this box.

Names In VBA

Fig. 4. shows that portion of the Excel object model that applies to Names.

Fig. 4.

Names can be manipulated in VBA just as they can in Excel, A workbook has a Names collection to which all Names belong, and the collection can be added to, deleted from, and read from. Creating a Name is a relatively straight-forward operation, just having to define both the Name and the RefersTo value

ActiveWorkbook.Names.Add Name:="myRange", RefersTo:="=!H5:M10"

In addition, Names can be defined in a shortcut fashion, using a range object directly, such as

Selection.Name = "myRange"

Range("H5:M10").Name = "myRange"

Range(Cells(5,8),Cells(10,13)) = "myRange"

etc.

The above are all examples that create a global (workbook) Name. Creating local (worksheet) Names is just as easy

ActiveWorkbook.Names.Add Name:="myRange", _
    RefersTo:="=" & ActiveSheet.Name & "!H5:M10"

ActiveSheet.Names.Add Name:="myRange", _
    RefersTo:="=H5:M10"

With ActiveSheet
    .Range("H5:M10").Name = .Name & "!H5:M10"
End With

Deleting a Name is also straight-forward. At least it is for a worksheet Name, or if there are not both global and local Names with the same name value (see later in 'Duplicate Names in VBA')

Activeworkbook.Names("myRange").Delete

A range Name is similar to any other range, and can be used in the same way, such as

MsgBox Range("myRange").Address

Again, this gets more complex if there are both global and local Names with the same name value (see later in 'Duplicate Names in VBA').

However, getting the value of a non-range name is a little trickier. The following code doesn't return the RefersTo value, but rather the RefersTo formula

MsgBox ActiveWorkbook.Names("myRange").RefersTo

that is a value of =Sheet1!H5:M10 is returned. To get the value, that is without the leading =, then we use

MsgBox Evaluate(ActiveWorkbook.Names("myRange").RefersTo)

Duplicate Names In VBA

In Excel, global and local Names are managed by Excel, working from the active worksheet and the Name. In VBA, you need to manage all of that yourself, as referring to a Name by it's workbook or worksheet object does not necessarily get the correct Name. Consider the following situation. Activate Sheet1 and create a global name with

Activeworkbook.Range("A1:A5").Name = "myRange"

and then activate Sheet2 and create a local (worksheet) Name with

Activesheet.Range("A1:A5").Name = Activesheet.Name & "!myRange"

Whilst having a global and local Name defined within the same workbook, it would seem a simple matter to be able to retrieve the Names, and their values. And so it is, if we retrieve the local (worksheet) Name. That is

MsgBox Activesheet.Names(myRange)

This shows a value of =Sheet2!H5:M10 (or whatever range you defined) as expected. It becomes more complex if we try to retrieve the global (workbook) Name in the same way.

Try to retrieve the same global Name with

MsgBox Activeworkbook.Names(myRange)

The MsgBox shows =Sheet2!H5:M10 as before. In other words, although you may have thought that you were retrieving the global name, you actually retrieve the local name. This seems to be true whenever there is a global and a local name of the same name, regardless of the order in which they were created.

What is happening here is that when there are global (workbook) and local (worksheet) Names of the same value, the global name is 'hidden ' by the local Name, even if it is not the worksheet of that local Name that is active. A local name, whilst applying to a particular worksheet, is still a name within that workbook, and can be found in the workbook Names collection. To get at the global Name when there might be a local same Name requires a bit of effort. My testing suggests this is true regardless of the order created.

Test Type Of Name

The following function is a general utility to determine whether a name is a global (workbook) name, or a local (worksheet) name

Using this function, you can easily test whether a Name exists within a workbook, and what type of Name it is.

'---------------------------------------------------------------------
Public Function NameContainer(FindVal, Optional Sh)
'---------------------------------------------------------------------
Dim nme As Name
Dim sName As String

    If IsMissing(Sh) Then Set Sh = ActiveSheet

    If VarType(Sh) = 8 Then			'string
        Set Sh = Worksheets(Sh)
    End If

    On Error Resume Next
        sName = Sh.Names(FindVal).Name
        If sName <> "" Then
            NameContainer = "Worksheet"
        Else
            For Each nme In Sh.Parent.Names
                If nme.Name Like "*" & FindVal Then
                    If Not nme.Name Like "*!" & FindVal Then
                        NameContainer = "Workbook"
                         Exit For
                     End If
                 End If
             Next nme
        End If

    On Error GoTo 0

End Function

This function is called with the Name to be tested, and optionally the worksheet (either as a worksheet object or by the worksheet name) that the local Name is to be tested against. If there is an instance of that local Name on that worksheet, the function returns the value Worksheet. If the worksheet test fails, but there is a global Name of that name value, it returns Workbook.

If this function returns Worksheet as the Name and you suspect that there is also a workbook Name of that value, call the function again with another worksheet, to force it.

This function could be easily enhanced to return all instances of that Name type for a workbook.

Return A Name Object

The following function is another general utility that returns a Name object at the requested level. If a local Name is required, either activate the appropriate worksheet, or pass the worksheet (as a worksheet object or by name) as the second argument, for a global Name, leave it blank.

'---------------------------------------------------------------------
Public Function GetName(FindVal, Optional Sh) As Object
'---------------------------------------------------------------------
Dim nme As Name
Dim sName As String

    If VarType(Sh) = 8 Then			'string
        Set Sh = Worksheets(Sh)
    End If

    On Error Resume Next
    If Not IsMissing(Sh) Then
        Set GetName = Sh.Names(FindVal)
    Else
        For Each nme In Activeworkbook.Names
            If nme.Name Like "*" & FindVal Then
                If Not nme.Name Like "*!" & FindVal Then
                    Set GetName = nme
                    Exit For
                End If
            End If
        Next nme
    End If

End Function

This second function works in a similar manner to the first, but returns a Name object. As before, you can specify to obtain the Workbook or Worksheet Name, or let it default. If the Name does not exist, the function returns an object of 'Nothing'.

Miscellany

A minor annoyance in Excel is the width of the Names dialog box. Often the defined Names are wider than the box, and cannot be easily identified within that dropdown. The following code provides a method to widen the Names dialog box.

Public Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
   (ByVal lpClassName As String, _
    ByVal lpWindowName As String) As Long

Public 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

Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" _
   (ByVal hwnd As Long, _
    ByVal wMsg As Long, _
    ByVal wParam As Long, _
    lParam As Any) As Long

'-----------------------------------------------------------------
Public Sub WidenNameBox()
'-----------------------------------------------------------------
Dim Res As Long
Const CB_SETDROPPEDWIDTH = &H160
Const cWidth = 200

    Res = SendMessage( _
            FindWindowEx( _
                FindWindowEx( _
                    FindWindow("XLMAIN", Application.Caption) _
                , 0, "EXCEL;", vbNullString) _
            , 0, "combobox", vbNullString), _
          CB_SETDROPPEDWIDTH, cWidth, 0)
End Sub

References

If you use Excel Names to any great degree, you might want to take a look at Jan Karel Pieterse's NameManager utility. This utility provides a fully developed GUI driven Name Managament facility. You can find it on the Office Automation MVP page, or at Decision Models.

Acknowledgements

Much of the information presented in this web page has been gleaned from my use of Excel Names, and from items posted in the Excel newsgroups. I would especially acknowledge the contributions of Dave Peterson, who has supplied many insights into the vagaries of Names.

As ever, thanks to Frank Kabel who proofed this document and corrected many of my errors and typographical mistakes, as well as providing his own thoughts of this facility.

Downloads

This page can be downloaded as a zipped Word document here (41Kb)