You’ve named ranges in a worksheet by highlighting a range and typing a name in the Name box to the left of the formula field. Perhaps you’ve also created more complicated names containing formulas—such as for finding the last row in a column. The ability to set a name to a range makes it much easier to write formulas and set tables.

The ability to create and manipulate names is also available in VBA and provides the same benefits as naming ranges in a worksheet: You can store, for example, a new range in a name.

This chapter explains different types of names and the various ways you can use them.

Names can be global—that is, available anywhere in the workbook—or local—available only on a specific worksheet. With local names, you can have multiple references in the workbook with the same name. Global names must be unique to the workbook.

In previous versions of Excel, it was difficult to tell whether you were looking at a global or local name—you had to be on the correct sheet and compare the list of names on different sheets. With Excel 2007, you have the Name Manager dialog box, which lists all the names in a workbook, even a name that has been assigned to both the global and local levels. The Scope column lists the scope of the name, whether it is the workbook or a specific sheet, such as Sheet1.

For example, in Figure 8.1, the name Apples is assigned to Sheet1, but also to the workbook.

Figure 8.1. The Name Manager lists all local and global names.

If you record the creation of a named range and then view the code, you see something like this:

ActiveWorkbook.Names.Add Name:="Fruits", RefersToR1C1:="=Sheet2!R1C1:R6C6"

This creates a global name "Fruits", which includes the range A1:F6 (R1C1:R6C6). The formula is enclosed in quotes, and the equal sign in the formula must be included. Also, the range reference must be absolute (include the $ sign) or in R1C1 notation. If the sheet on which the name is created is the active sheet, the sheet reference does not have to be included; however, it can make the code easier to understand.

Note

If the reference is not absolute, the name may be created, but will not point to the correct range. For example, if you run this line of code

ActiveWorkbook.Names.Add Name:="Citrus", _
   RefersToR1C1:="=Sheet1!A1"

the name is created in the workbook. As you can see in Figure 8.2, however, it hasn’t actually been assigned to the range.

Figure 8.2. Cell A1 doesn’t have the name Citrus assigned to it because the name formula lacks absolute referencing and is not properly recognized by Excel.

To create a local name, include the sheet name:

ActiveWorkbook.Names.Add Name:="Sheet2!Fruits", _
    RefersToR1C1:="=Sheet2!R1C1:R6C6"

Or, specify that the Names collection belongs to a worksheet:

Worksheets("Sheet1").Names.Add Name:="Fruits", _
    RefersToR1C1:="=Sheet1!R1C1:R6C6"

The preceding example is what you would learn from the macro recorder. There is a simpler way:

Range("A1:F6").Name = "Fruits"

Or, for a local variable only, you can use this:

Range("A1:F6").Name = "Sheet1!Fruits"

When creating names with this method, absolute referencing is not required.

Although this is much easier and quicker than what the macro recorder creates, it is limited in that it works only for ranges. Formulas, strings, numbers, and arrays require the use of the Add method.

The Name property of the name ObjectName is an object but still has a Name property. The following line renames an existing name:

Names("Fruits").Name = "Produce"

Fruits no longer exists; Produce is now the name of the range.

When you are renaming names in which a local and global reference both carry the same name, the previous line renames the local reference first.

If Range("A1:F6").Name = "Fruits" exists early in the code and then Range("A1:F6").Name = "Produce" is added later, Produce overwrites Fruits, as shown in Figure 8.3. An attempt to access the local name Fruits later in the program creates an error because it no longer exists.

Figure 8.3. It’s easy to overwrite an existing name if you aren’t careful. The local name Fruits has been overwritten by the local name Produce. The name Fruits you see is the global variable, not the local one.

Use the Delete method to delete a name:

Names("ProduceNum").Delete

An error occurs if you attempt to delete a name that doesn’t exist.

With Excel 2007, you can now add comments about names. You can add any additional information that you want, such as why the name was created or where it is used. To insert a comment for the local name LocalOffice, do this:

ActiveWorkbook.Worksheets("Sheet7").Names("LocalOffice").Comment = _
"Holds the name of the current office"

The comments will appear in a column in the Name Manager, as shown in Figure 8.4.

Figure 8.4. You can add comments about names to help you remember their purpose.

The most common use of names is for storing ranges. But names can store more than that. After all, that’s what they’re for: Names store information. They make it simple to remember and use potentially complex or large amounts of information. And, unlike variables, names remember what they store beyond the life of the program.

We’ve already covered creating range names, but we can also assign names to name formulas, strings, numbers, and arrays.

Formulas

The syntax for storing a formula in a name is the same as for a range because the range is, in essence, a formula:

Names.Add Name:="ProductList", _
    RefersTo:="=OFFSET(Sheet2!$A$2,0,0,COUNTA(Sheet2!$A:$A))"

The preceding code allows for a dynamic named column, which is very useful for creating dynamic tables or for referencing any dynamic listing on which calculations may be performed, as shown in Figure 8.5.

Figure 8.5. Dynamic formulas can be assigned to names.

Strings

When using names to hold strings, such as the name of the current fruit producer, enclose the string value in quotes. Because there is no formula involved, an equal sign is not needed:

Names.Add Name: = "Company", RefersTo:="CompanyA"

Figure 8.6 shows how the coded name would appear in the Name Manager window.

Figure 8.6. A string value can be assigned to a name.

Using Names to Store Values

Because names do not lose their references between sessions, this is a great way to store values as opposed to storing values in cells from which the information would have to be retrieved. For example, to track the leading producer between seasons, create a name Leader. If the new season’s leading producer matches the name reference, a special report comparing the seasons could be created. The other option is to create a special sheet to track the values between sessions and then retrieve the values when needed. With names, the values are readily available.

The following procedure shows how cells in a variable sheet are used to retain information between sessions:

Sub NoNames(ByRef CurrentTop As String)
TopSeller = Worksheets("Variables").Range("A1").Value
If CurrentTop = TopSeller Then
    MsgBox ("Top Producer is " & TopSeller & " again.")
Else
    MsgBox ("New Top Producer is " & CurrentTop)
End If
End Sub

The following procedure shows how names are used to store information between sessions:

Sub WithNames()
If Evaluate("Current") = Evaluate("Previous") Then
    MsgBox ("Top Producer is " & Evaluate("Previous") & " again.")
Else
    MsgBox ("New Top Producer is " & Evaluate("Current"))
End If
End Sub

If Current and Previous are previously declared names, you access them directly rather than create variables to pass them in. Note the use of the Evaluate method to extract the values in names. The string being stored cannot have more than 255 characters.

NumofSales = 5123
Names.Add Name:="TotalSales", RefersTo:=NumofSales

Names.Add Name:="TotalSales", RefersTo:=5123

NumofSales = Names("TotalSales").Value

NumofSales = [TotalSales]

Tables

Excel tables share some of the properties of defined names, but also have their own unique methods. Unlike defined names, which are what we’re used to dealing with, tables cannot be created manually—that is, you cannot select a range on a sheet and type a name in the Name field. However, you can manually create them via VBA.

Tables aren’t created using the same method as the defined names. Instead of Range(xx).Add or Names.Add, we use ListObjects.Add.

To create a table from cells A1:F6, and assuming the table has column headers, as shown in Figure 8.7, do this:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$F$6"), , xlYes).Name = _
"Table1"

Figure 8.7. You can assign a special name to a data table.

xlSrcRange (the SourceType) tells Excel the source of the data is an Excel range. You then need to specify the range (the source) of the table. If you have headers in the table, include the row when indicating the range. The next argument, not used in the preceding example, is the LinkSource, a Boolean indicating whether there is an external data source and is not used if the SourceType is xlSrcRange. The xlYes lets Excel know the data table has column headers; otherwise, Excel automatically generates them. The final argument, not shown in the preceding example, is the destination, used when the SourceType is xlSrcExternal, indicating the upper-left cell where the table will begin.

Sub NamedArray()
Dim myArray(10, 5)
Dim i As Integer, j As Integer
'The following For loops fill the array myArray
For i = 1 To 10
    For j = 1 To 5
        myArray(i, j) = i + j
    Next j
Next i
'The following line takes our array and gives it a name
Names.Add Name:="FirstArray", RefersTo:=myArray
End Sub

Reserved Names

Excel uses local names of its own to keep track of information. These local names are considered reserved, and if you use them for your own references, they might cause problems.

Highlight an area on a sheet. Then from the Page Layout ribbon, select Print Area, Set Print Area.

As shown in Figure 8.8, a Print_Area listing is in the Range Name field. Deselect the area and look again in the Range Name field. The name is still there. Select it, and the print area previously set is now highlighted. If you save, close, and reopen the workbook, Print_Area is still set to the same range. Print_Area is a name reserved by Excel for its own use.

Figure 8.8. Excel creates its own names.

Caution

Each sheet has its own print area. Also, setting a new print area on a sheet with an existing print area overwrites the original print area name.

Luckily, Excel does not have a large list of reserved names:

• Criteria

• Database

• Extract

• Print_Area

• Print_Titles

Criteria and Extract are used when the Advanced Filter (on the Data ribbon, select Advanced Filter) is configured to extract the results of the filter to a new location.

Database is no longer required in Excel, but some features, such as Data Form, do recognize it. Older versions of Excel used it to identify the data you wanted to manipulate in certain functions.

Print_Area is used when a print area is set (from the Page Layout ribbon, select Print Area, Set Print ARea) or when Page Setup options that designate the print area from the Page Layout ribbon, Scale) are changed.

Print_Titles is used when print titles are set (Page Layout, Print Titles).

These names should be avoided and variations used with caution. For example, if you create a name PrintTitles, you might accidentally code this:

Worksheets("Sheet4").Names("Print_Titles").Delete

You’ve just deleted the Excel name rather than your custom name.

Names are incredibly useful, but you don’t necessarily want to see all the names you’ve created. Like many other objects, names have a Visible property. To hide a name, set the Visible property to False. To unhide a name, set the Visible property to True:

Names.Add Name:="ProduceNum", RefersTo:="=$A$1", Visible:=False

You can use the following function to check for the existence of a user-defined name, even a hidden one, but it does not return the existence of Excel’s reserved names. It’s a handy addition to your arsenal of “programmer’s useful code”:

Function NameExists(FindName As String) As Boolean
Dim Rng As Range
Dim myName As String
On Error Resume Next
myName = ActiveWorkbook.Names(FindName).Name
If Err.Number = 0 Then
    NameExists = True
Else
    NameExists = False
End If
End Function

The preceding code is also an example of how to use errors to your advantage. If the name for which you are searching doesn’t exist, an error message is generated. By adding the On Error Resume Next line at the beginning, you force the code to continue. Then you use Err.Number to tell you whether you ran into an error. If you didn’t, Err.Number is zero, which means the name exists; otherwise, you had an error, and the name does not exist.

Sub ImportData()
' This routine imports sales.csv to the data sheet
' Check to see whether any stores in column A are new
' If any are new, then add them to the StoreList table
Dim WSD As Worksheet
Dim WSM As Worksheet
Dim WB As Workbook

Set WB = ThisWorkbook
' Data is stored on the Data worksheet
Set WSD = WB.Worksheets("Data")
' StoreList is stored on a menu worksheet
Set WSM = WB.Worksheets("Menu")

' Open the file..This makes the csv file active
Workbooks.Open Filename:="C:Sales.csv"
' Copy the data to WSD and close
ActiveWorkbook.Range("A1").CurrentRegion.Copy Destination:=WSD.Range("A1")
ActiveWorkbook.Close SaveChanges:=False
' Find a list of unique stores from column A
FinalRow = WSD.Cells(WSD.Rows.Count, 1).End(xlUp).Row
WSD.Range("A1").Resize(FinalRow, 1).AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=WSD.Range("Z1"), Unique:=True

' For all the unique stores, see whether they are in the
' current store list.
FinalStore = WSD.Range("Z" & WSD.Rows.Count).End(xlUp).Row
WSD.Range("AA1").Value = "There?"
WSD.Range("AA2:AA" & FinalStore).FormulaR1C1 = _
    "=ISNA(VLOOKUP(RC[-1],StoreList,1,False))"

' Find the next row for a new store. Because StoreList starts in A1
' of the Menu sheet, find the next available row
NextRow = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row + 1

' Loop through the list of today 's stores.If they are shown
' as missing, then add them at the bottom of the StoreList
For i = 2 To FinalStore
    If WSD.Cells(i, 27).Value = True Then
        ThisStore = Cells(i, 26).Value
        WSM.Cells(NextRow, 1).Value = ThisStore
        WSM.Cells(NextRow, 2).Value = _
            InputBox(Prompt:="What is name of store " _
            & ThisStore, Title:="New Store Found")
        NextRow = NextRow + 1
    End If
Next i

' Delete the temporary list of stores in Z &AA
WSD.Range("Z1:AA" & FinalStore).Clear

' In case any stores were added, re-define StoreList name
FinalStore = WSM.Range("A" & WSM.Rows.Count).End(xlUp).Row
WSM.Range("A1:B" & FinalStore).Name = "StoreList"

' Use VLOOKUP to add StoreName to column B of the dataset
WSD.Range("B1").EntireColumn.Insert
WSD.Range("B1").Value = "StoreName"
WSD.Range("B2:B" & FinalRow).FormulaR1C1 = "=VLOOKUP(RC1,StoreList,2,False)"

' Change Formulas to Values
WSD.Range("B2:B" & FinalRow).Value = Range("B2:B" & FinalRow).Value

'Release our variables
Set WB = Nothing
Set WSD = Nothing
Set WSM = Nothing
End Sub

In the next chapter, you’ll learn how code can be written to run automatically based on users’ actions, such as activating a sheet or selecting a cell. This is done with events, which are actions in Excel that can be captured and used to your advantage.