VBA: Recall and Reference Defined Ranges in Excel

Naming a range is particularly helpful when working in VBA. Naming a range allows you to link directly to a particular range regardless of its address. Adding rows and columns will result in a change of a ranges address, but the exact range can still be linked to via its name.

The name box is located just to the left of the formula bar.

Before a range is named the address of the first cell selected in the range will appear in the name box. To change the name simply click in the name box, delete the address and change the name to whatever you please.

Note: A range name can contain no spaces.

After you have name your cell you can recall it in excel. Let's give it a try.

1. Move to another cell
2. Click the dropdown box next to the name field
3. Find the range name you created
4. Click the range this will return you to the range you defined earlier

Now that you have created your name range you can use it in any formula that requires a range input, simply input the name instead of the range.

The following would return the sum of named range "test"

=sum(test)

The range method allows you to directly refer to you namee range. Simply include the range name in quotes where the address would normally go.

For Instance: to select a range named "test"

Range("test").select

For more examples of using the Range Method click here.

Note: In order to select a range in VBA the workbook containing it must be the active workbook.

One of the main pitfalls of those beginning to learn to write VBA is their use of direct cell references.

For instance lets say there is a file path cell B2 that I want to open. To do this with a direct reference I could use the following code.

Dim filepath as string

filepath=range("B2").value

workbook.open(filepath)

This will work fine but assume a new row is insert above cell B2. Cell B2 will then be moved to B3. To prevent this from happening use a name range.

In this case I have named the range B2 "filepathrange". Now I do not have to worry about referring to data in the wrong cell if rows and columns are added or deleted.

Dim filepath as string

filepath=range("filepathrange").value

workbook.open(filepath)

in the next module I will cover adding, deleting, and clearing ranges. When your ready to proceed click here.