Adding Names

If you record the creation of a named range and then view the code, you see something similar to

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.

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: Range("A1:F6").Name = "Sheet1!Fruits"

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 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 on, "Produce" overwrites "Fruits", as shown in Figure 7.4. An attempt to access the local name "Fruits" later in the program creates an error because it no longer exists.

Figure 7.4

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.

Figure 7.4

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.

0 0

Post a comment