VBA Named Range

Overview on Excel VBA Named Range

In excel we learned how to name a range and use it. Now we will learn how to use this name range in VBA and in addition to that we will also learn how to name a range from VBA. Range is a crucial part of a worksheet as it contains the data for us. Specifically giving names to ranges help us to identify which part of the range contains which data.

Let us begin with how can we name a range in excel and then use it in VBA. In order to name a group of ranges in excel, we simply select the ranges as follows,

Now there is a box where it is mentioned as A1 we click on it and give it a name.

The box is called a name box and we use it to give a name to our range. For the current example, I have given it as a named range. Now once we have given the range name how do we use it in VBA. Suppose we had to change the interior color of the above range previously in VBA what we did in VBA is we referred to the range as follows,

Worksheets(“Sheet1”).Range(“A1:B5”).Interior.Color = Some Color code

Now since we have given the range a name we can now do the following to change the color of the range as follows,

Worksheets(“Sheet1”).Range(“NEW”).Interior.Color = Some Color code

NEW is the range name we have given to cell selection A1:B5. This is how we use name ranges in VBA once we have named them.

Now we can also name the ranges using VBA too well it is a bit lengthy process than above but let us go through it.

To name a range in a worksheet by VBA we use the following code as follows,

Set myWorksheet = ThisWorkbook.Worksheets("Named Range")

Set myNamedRangeWorksheet = myWorksheet.Range("Range Value”)

How to Use Named Range in Excel VBA?

There are two methods to use the Named Range in Excel VBA:

1. First is that we name a range in excel and then use it in VBA.
2. The second method is we make a named range in VBA itself and use its properties.

Below are the different examples to use the Named Range in Excel:

Example #1

For the first example let us use the name range we used above by making it in excel worksheet. Follow the below steps:

Step 1: Select the range which we want to name first as follows,

Step 2: There is a name box above the range we use that to give a name to the range, for this example, I have named the ranged as New.

Step 3: Now let us go to VBA, click on Developer’s tab to click on Visual Basic to get into VBA.

Step 4: Once we are in VBA we need to insert a module so that we can write code in it. Do as follows,

Step 5: Now let us start writing the code, to do that we need to name the macro first as follows,

Code:

Sub Sample()

End Sub

Step 6: Write the following code to activate the worksheet first in order to use the name range.

Code:

Sub Sample()

Worksheets("Sheet1").Activate

End Sub

Step 7: Now let us use our name range as follows,

Code:

Sub Sample()

Worksheets("Sheet1").Activate
Range("NEW").Value = 10

End Sub

Step 8: Once we run the above code we can see that every cell in our name range has the same value as follows,

Step 9: Let us also check if we can change the properties of the cell, Let us change the color of the range by using the name range as follows,

Code:

Sub Sample()

Worksheets("Sheet1").Activate
Range("NEW").Value = 10
Range("NEW").Interior.Color = 255

End Sub

Step 10: Run the above code by pressing F5 or from the run button and see the following result,

Example #2

We will first create a named range by using VBA and the use the properties of the name range in VBA itself. Follow the below steps:

Step 1: Now let us start writing the code, to do that we need to name the macro first as follows,

Code:

Sub Sample1()

End Sub

Step 2: Declare a variable which will store the name of the name range as follows,

Code:

Sub Sample1()

Dim myRangeName As String

End Sub

Step 3: Now let us set the name range as follows, in this example whatever we have selected as the range will be our named range,

Code:

Sub Sample1()

Dim myRangeName As String
myRangeName = "namedRangeFromSelection"

End Sub

This is what I have in selection for named range cell range A1:C5 in worksheet 2,

Step 4: Now let us name the range by the following code,

Code:

Sub Sample1()

Dim myRangeName As String
myRangeName = "namedRangeFromSelection"
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=Selection

End Sub

Step 5: Run the above code we can see that we have created the name range in worksheet 2 by the name of namedrangefromselection,

Step 6: Now let us use this name ranged as we used in the earlier example as follows,

Code:

Sub Sample1()

Dim myRangeName As String
myRangeName = "namedRangeFromSelection"
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=Selection
Range("namedRangeFromSelection").Value = 10
Range("namedRangeFromSelection").Interior.Color = 255

End Sub

Step 7: Run the above code from the F5 button and see the result as follows,

Things to Remember

• Named Ranges are the name given for a group or range of cells.
• Named range is used to refer to the specific range of cells which needs to be accessed later on.
• We can name a range in both excel or VBA.
• While making a named range in VBA we need to use Add name function in VBA.

Recommended Articles

This is a guide to VBA Named Range. Here we discuss how to use Named Range in Excel VBA along with practical examples and downloadable excel template. You can also go through our other suggested articles –

1. Step by Step Guide to VBA Value
2. How to Edit Drop Down List in Excel?
3. VBA Subscript out of Range
4. Import Data into Excel