Many a time while working with VBA in Excel, we have to use a Named Range. In this article, I’ll show you how you can create and refer to a Named Range with VBA in Excel with proper examples and illustrations.
Named Range in Excel VBA (Quick View)
It assigns the Named Range “Dataset” of the worksheet “Sheet1” and of the workbook “VBA Named Range” to the Range object called Rng.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
2 Simple Ways to Use Named Range in Excel VBA
A Named Range is a group of cells that has been provided with a specific name in an Excel worksheet.
You can create a Named Range manually by first selecting the desired range, then going to Formulas > Define Name > Define Name tool in the Excel toolbar.
Today our objective is to learn to create and refer to a Named Range with Visual Basic Application (VBA) in Excel.
1. How to Create a Named Range in Excel VBA
First, we’ll see how to create a Named Range with VBA.
To create a Named Range, first, you have to assign the desired range to a Range object.
Here I have assigned the range B3:D13 to a Range object called Rng.
Set Rng = Range("B3:D13")
Or if the range is of another worksheet other than the active one, you have to put the name of the worksheet before.
Here I’ve put the name of the worksheet “Sheet1” before.
Set Rng = Worksheets("Sheet1").Range("B3:D13")
Then you have to assign it as a Named Range. I have named it “Dataset”.
ThisWorkbook.Names.Add Name:="Dataset", RefersTo:=Rng
Or you can put the name of the workbook other than ThisWorkbook, if the workbook isn’t active.
Here I’ve put the name of the workbook “VBA Named Range.xlsm” in the front.
Workbooks("VBA Named Range.xlsm").Names.Add Name:="Dataset", RefersTo:=Rng
So the complete VBA code will be:
⧭ VBA Code:
Sub Create_Named_Range()
Set Rng = Worksheets("Sheet1").Range("B3:D13")
ThisWorkbook.Names.Add Name:="Dataset", RefersTo:=Rng
End Sub
⧭ Output:
Run the Macro (Create_Named_Range) and you’ll find the range B3:D13 assigned with the name Dataset.
Read More: Excel VBA to Create Named Range from Selection (5 Examples)
Similar Readings
- How to Delete All Named Ranges in Excel (2 Ways)
- Use Dynamic Named Range in an Excel Chart (A Complete Guide)
- How to Name a Group of Cells in Excel (3 Methods +1 Bonus)
- Change Scope of Named Range in Excel (3 Methods)
- How to Paste Range Names in Excel (7 Ways)
2. How to Refer to a Named Range in Excel VBA
Now we’ll see how we can refer to a Named Range with VBA.
To refer to a Named Range, you have to enter the name within a Range object in VBA.
For example, to access the Named Range “Dataset”, we have to use:
Set Rng = Range("Dataset")
Or you can use the name of the worksheet in the front if it’s not the active one.
Here it’s Sheet1.
Set Rng = Worksheets("Sheet1").Range("Dataset")
You can put the name of the workbook in front also.
Here it’s VBA Named Range.xlsm.
Set Rng = Workbooks("VBA Named Range").Worksheets("Sheet1").Range("Dataset")
Now, the Named Range “Dataset” is assigned to the Range object called Rng.
You can use any property available of the Range object.
For example, you can use the Select property of the Range object.
Rng.Select
Run the code. And it’ll select the Named Range “Dataset”.
Or you can use the Cells property of the Range object of VBA.
MsgBox Rng.Cells(2, 1)
It will show the value of the cell (2,1) of the Named Range “DataSet”, A Tale of Two Cities.
In this way, you can access all the properties of the Range object of VBA with the Named Range.
Read More: How to Edit Named Range in Excel
Conclusion
Using these methods, you can create and refer to a Named Range with VBA. Do you have any questions? Feel free to ask us.