In Excel VBA, you can assign a specific name to a cell or a range of cells. Named ranges can make your code easier to understand and maintain.
In this tutorial, I will show you four ways of creating named ranges in Excel VBA using the ‘Names.Add‘ method.
Explanation of the ‘Names. Add’ Method
The ‘Names. Add’ method defines a name for a cell or a cell range.
Below is the syntax of the Names. Add method:
expression. Add (Name, RefersTo, Visible, MacroType, ShortcutKey, Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1, RefersToR1C1Local)
expression is a variable standing for a ‘Names’ object.
The method has 11 optional arguments of the variant data type. However, the most commonly used ones are ‘Name’ and ‘RefersTo,’ which are explained in the table below:
| Argument | Description |
|---|---|
| Name | Specifies the text string to use as the name. Names must begin with a letter or an underscore and cannot be formatted as cell references (e.g., AC2), include spaces, or conflict with an existing name. |
| RefersTo | Indicates the cell reference the name refers to using A1-style notation.If the reference is not specified, ‘Nothing’ is returned. |
Example #1: VBA to Create a Static Named Range Scoped to a Worksheet
You can create a named range that is only accessible and applicable within the worksheet where you created it, and you cannot use it in other worksheets in the same workbook.
Suppose you have the data range below on Sheet1 of the current workbook:
You can give the data range the name ‘Electronics’ scoped to Sheet1 of the current workbook by using the code below:
Sub NamedRangeWorksheetScope()
Dim ws As Worksheet
Dim RefersToRangeWorksheet As Range
Dim RangeName As String
Set ws = ThisWorkbook.Worksheets("Sheet1")
Set RefersToRangeWorksheet = ws.Range("A1:D6")
RangeName = "Electronics"
ws.Names.Add Name:=RangeName, RefersTo:=RefersToRangeWorksheet
End SubWhen you run the code, it names the data range ‘Electronics’ as shown below:
Note: Select the data range to see the name in the ‘Name Box.’
Also read: VBA to Copy Range to Email Body
Example #2: VBA to Create a Static Named Range Scoped to a Workbook
In VBA, you can create a named range that is accessible and applicable within all worksheets in a workbook.
Assume you have the data range below on Sheet2 of the current workbook:
You can give the data range the name ‘ProductBrands,’ scoped to the current workbook using the subroutine below:
Sub NamedRangeWorkbookScope()
Dim ws As Worksheet
Dim RefersToRangeWorkbook As Range
Dim RangeName As String
Set ws = ThisWorkbook.Worksheets("Sheet2")
Set RefersToRangeWorkbook = ws.Range("A1:A10")
RangeName = "ProductBrands"
ThisWorkbook.Names.Add Name:=RangeName, _
RefersTo:=RefersToRangeWorkbook
End SubWhen you execute the code, it names the data range ‘ProductBrands’ as shown below:
Also read: VBA to Print Range of Cells to PDF
Example #3: VBA to Create a Named Range From a Selection
In Excel VBA, you can create a named range from a selection on a worksheet. You can scope the name to a worksheet or workbook, as shown in Examples #1 and #2.
Assume you have the data range below on Sheet3 of the current workbook with the cell range A2:A6 selected:
You can use the VBA code below to name the selected cell range ‘ElectronicGadgets’ and scope the name to the current workbook:
Sub NamedRangeFromSelection()
Dim RangeName As String
RangeName = "ElectronicGadgets"
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=Selection
End SubWhen you run the subroutine, it names the selection ‘ElectronicGadgets’ as shown below:
Also read: VBA to Find Value in Range in Excel
Example #4: VBA to Create a Dynamic Named Range
You can use the ‘Range.Find’ method in a subroutine to create a dynamic named range that expands or contracts automatically as data is added or removed.
Assume you have the following data range that keeps adjusting as data is added or removed on Sheet4 of the current workbook:
You can use the code below to name the data range ‘ProductList’ and scope it to the current workbook:
Sub NamedRangeDynamic()
Dim ws As Worksheet
Dim firstRow As Long
Dim lastRow As Long
Dim firstColumn As Long
Dim lastColumn As Long
Dim NamedRangeDynamic As Range
Dim RangeName As String
Set ws = ThisWorkbook.Worksheets("Sheet4")
firstRow = 1
firstColumn = 1
RangeName = "ProductList"
With ws.Cells
lastRow = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
lastColumn = .Find(What:="*", LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
Set NamedRangeDynamic = .Range(.Cells(firstRow, firstColumn), .Cells(lastRow, lastColumn))
End With
ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=NamedRangeDynamic
End SubWhen you execute the code, it names the data range ‘ProductList’ as shown below:
If you delete some rows from the data range and rerun the code, the named range adjusts accordingly:
If you add data to the data range, the named range will also adjust accordingly.
The subroutine uses the ‘Range.Find’ method to determine the last row and last column with data on the worksheet.
Then, starting at cell A1 (the cell at the intersection of row 1 and column 1 specified in the code) to the cell at the intersection of the last column and last row with data, the code creates a dynamic range object and names it ‘ProductList.’
Benefits of Using Named Ranges in Excel
Named ranges offer an easy way to reference specific cell ranges by a custom name instead of using standard row/column references, and they have many benefits:
- When you assign a meaningful name to a range of cells instead of referring to it by its cell references (e.g., A1:E10), you make formulas and references in the worksheet more readable and easier to understand, especially in complex worksheets.
- Named ranges simplify formula creation and editing. Instead of manually typing cell references, users can select named ranges from a dropdown list, reducing the chances of errors and increasing efficiency.
- Named ranges can refer to dynamic ranges that automatically adjust as the data in the worksheet changes. Dynamic name ranges are helpful in scenarios where the data range expands or contracts frequently when data is added or removed.
- When collaborating on worksheets, named ranges clarify the purpose of specific data ranges or formulas. This clarity makes it easier for multiple users to understand and maintain the worksheet over time.
- Named ranges improve formula auditing and debugging by providing descriptive names instead of cell references. The descriptive names make tracing and troubleshooting formulas easier, especially in large and complex workbooks.
- Named ranges promote consistency in formulas and data references throughout the worksheet. When users use the same named range in multiple formulas, they ensure that they consistently reference the same data, reducing the risk of errors.
Some Tips When Using Named Ranges in Excel
Some things to be careful when creating named ranges in Excel:
- Avoid Spaces and Special Characters: It’s recommended to avoid using spaces or most punctuation in your names, which can cause errors. Instead, use underscores or CamelCase to separate words.
- Keep Names Short and Descriptive: Another best practice is to keep the names short and concise. Long and complicated names can be difficult to remember and may lead to errors.
- Use Unique Names: Ensure that each named range has a unique name that does not conflict with existing cell references or other range names in Excel.
- Document Your Named Ranges: Especially in complex workbooks, it’s helpful to keep a list or documentation of named ranges and their intended use to aid in future maintenance and understanding.
- Be Mindful of Scope: Excel allows you to define names that are scoped to a specific worksheet or to the entire workbook. Choose the appropriate scope based on how you plan to use the named range.
In this tutorial, I showed you four ways of creating named ranges in Excel VBA. I hope you found the tutorial helpful.
Other Excel articles you may also like:
