How to Use Named Range in Excel VBA (2 Ways)

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)

Refer to a Named Range with VBA in Excel

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.

Named Range in Excel

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.

Creating Named Range in Excel

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")

VBA Code to Create Named Range in Excel

Then you have to assign it as a Named Range. I have named it “Dataset”.

ThisWorkbook.Names.Add Name:="Dataset", RefersTo:=Rng

VBA Code to Create Named Range in Excel

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

VBA Code with Named Range in Excel

⧭ Output:

Run the Macro (Create_Named_Range) and you’ll find the range B3:D13 assigned with the name Dataset.

Named Range in Excel


Similar Readings:


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")

VBA Code to Refer to a Named Range in Excel

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")

Refer to a Named Range with VBA in Excel

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

VBA Code to Work with Named Range

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.


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.


Further Readings

Rifat Hassan

Rifat Hassan

Hello! Welcome to my profile. Here I will be posting articles related to Microsoft Excel. I am a passionate Electrical Engineer holding a Bachelor’s degree in Electrical and Electronic Engineering from the Bangladesh University of Engineering and Technology. Besides academic studies, I always love to keep pace with the revolution in technology that the world is rushing towards day by day. I am diligent, career-oriented, and ready to cherish knowledge throughout my life.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo