Excel 2010 Macro Adding and Removing Names
Adding Names
When you record the creation of a named range and view the code, you will 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 it must include the equal sign. Additionally, 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. Including it can make it easier to understand the code, though.
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 above example is what you would learn from the macro recorder. 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 you create names with this method, absolute referencing is not required. Even though this is much easier and quicker than what the macro recorder creates, it is limited to working only for ranges. Formulas, strings, numbers, and arrays require you to use 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”
Note that Produce is now the name of the range and Fruits does not exist anymore. The previous line renames the local reference first when names are renamed in which a local and global reference both carries the same name.
Removing Names
Use the Delete method to delete a name:
Names(“ProduceNum”).Delete
You will see an error if you try to delete a non-existent name.