8. Create and Manipulate Names in VBA

Excel Names

You have probably named ranges in a worksheet by highlighting a range and typing a name in the Name box to the left of the formula field. You also might have created more complicated names containing formulas. For example, perhaps you created a name with a formula that finds 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. For example, you can store a new range in a name.

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

Global Versus Local Names

Names can be global, which means they are available anywhere in the workbook. Names can also be local, which means they are 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. In fact, you had to be on the correct sheet and compare the list of names on different sheets. Beginning 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.

Adding 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. In addition, 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.

To create a local name, include the sheet name:

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

Alternatively, 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"

Alternatively, 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.

Deleting Names

Use the Delete method to delete a name:

Names("ProduceNum").Delete

An error occurs if you attempt to delete a name that does not exist.

Adding Comments

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

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

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

Types of Names

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

You have covered creating range names, but you 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 essentially a formula:

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

Figure 8.4. 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. If you were to include an equal sign, Excel would treat the value as a formula. Let Excel include the equal sign shown in the Name Manager.

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

Figure 8.5 shows how the coded name will appear in the Name Manager window.

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

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

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

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

Numbers

You can also use names to store numbers between sessions. Use this:

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

Alternatively, you can use this:

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

Notice the lack of quotes or an equal sign. Using quotes changes the number to a string. With the addition of an equal sign, the number changes to a formula.

To retrieve the value in the name, you have a longer and a shorter option:

NumofSales = Names("TotalSales").Value

or

NumofSales = [TotalSales]

Tables

Excel tables share some of the properties of defined names, but they also have their own unique methods. Unlike defined names, which are what you are used to dealing with, tables cannot be created manually. In other words, 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 are not created using the same method as the defined names. Instead of Range(xx).Add or Names.Add, use ListObjects.Add.

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

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

Figure 8.6. 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 that row when indicating the range. The next argument, which is 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. xlYes lets Excel know the data table has column headers; otherwise, Excel automatically generates them. The final argument, which is not shown in the preceding example, is the destination. This is used when the SourceType is xlSrcExternal, indicating the upper-left cell where the table will begin.

Using Arrays in Names

A name can also store the data stored in an array. The array size is limited by available memory. See Chapter 19, “Arrays,” for more information about arrays.

An array reference is stored in a name the same way as a numeric reference:

Because the name is referencing a variable, no quotes or equal signs are required.

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 tab, select Print Area, Set Print Area.

As shown in Figure 8.7, 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.7. Excel creates its own names.

Fortunately, 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 tab, select Advanced Filter) is configured to extract the results of the filter to a new location.

Database is no longer required in Excel. However, some features, such as Data Form, still recognize it. Legacy 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 tab, select Print Area, Set Print Area) or when Page Setup options that designate the print area (from the Page Layout tab, 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 have just deleted the Excel name rather than your custom name.

Hiding Names

Names are incredibly useful, but you don’t necessarily want to see all the names you have 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

Checking for the Existence of a Name

You can use the following function to check for the existence of a user-defined name, even a hidden one. Keep in mind that this function does not return the existence of Excel’s reserved names. Even so, this is a handy addition to your arsenal of “programmer’s useful code”:

The preceding code is also an example of how to use errors to your advantage. If the name for which you are searching does not 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 it 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.

Next Steps

In Chapter 9, “Event Programming,” you will 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.