VBA Coding Pattern: Named Range

Even if the reference address of Range is changed during VBA coding, if you use Named Range (named range), you can prevent the source code from changing regardless of the reference address change. Let's look at the following.

1. Range reference using absolute address

Among the many methods of referencing Range in Excel VBA, the most frequently used method is as follows.

Dim sVal As String
sVal = Range("B2").Value2
Debug.Print sVal

In Range(“B2”), “B2” represents the cell absolute address on the Excel sheet. When adding a row or column by changing the form of a sheet, “B2” must be changed to “B3” or “C2”. If the cell absolute address is only in one place in the VBA code, it can be easily changed.

If the change in the range to be referenced does not affect changes in the VBA code, assign a name to the range so that the VBA code refers to the name rather than the absolute address of the cell, and if the referenced location changes, the name You can change the cell address specified in .

2. Referencing a Range by Name

2.1. Create a Named Range

First, create a name for the Range by executing the following process. Formulas > Name Manager > New… run (See the following picture)

In the “New Name” window, set the name, range, description, and reference target.

Here, “scope” means the scope to refer to the name, and you can choose one of the two.

• Sheet (e.g. Sheet1): can only be referenced by name in VBA code written on that sheet
• Workbook: Full sheet, module, class, etc. can be referenced by name

For reference, the names defined in the sheet are copied together when copying the sheet. At this time, if you need to refer to the same name in multiple sheets, set it to “Sheet”, and if you do not need to refer to the same name, set it to “Workbook”. When you create a name, you can check the result as follows.

If you select the cell where the name is defined, the name is displayed in the name box.

If you select a cell whose name is not defined, the absolute address of the cell is displayed in the name box.

2.2. Write name reference code

After defining the name, write VBA code referring to the defined name. A simple example is:

Dim sVal As String
sVal = Range("BaseRange").Value2
Debug.Print sVal

2.3. Advantages of using names

If the absolute address is changed, the absolute address referred to by the corresponding name can be changed. It is convenient because there is no need to find and change the entire VBA source code.

When a column or row is added or deleted, the absolute address to which the name refers is automatically changed. For example, if there is a name referring to cell “B2”, adding a row between rows 1 and 2 will automatically change it to “B3”.

Improves readability when reading VBA code. To find out what “B2” means in the source code, you have to open and look at the sheet, but if you use names such as “BaseRange”, “TableListBase”, “ColumnListBase”, and “ValueListBase”, you can understand the meaning without opening the sheet. this makes it much simpler

additional information Use Excel Name Manager (microsoft.com) can be found in

---

In the above, we looked at the method and advantages of using a name for a Range. I hope it will help you with VBA coding, and if you have any questions, please leave a comment.