Excel VBA Named Range
We can select a cell or range of cells and give a name to it. After naming the cells we can refer to those cells by entering those defined names instead of a usual row or column references. When we work with a large amount of data to avoid referring to a particular cell or cells ranges we usually create named ranges and it allows us to refer to the required cell range through the name of the named range in excel VBA.
How to Create Named Ranges?
It is a walk in the park job to create named ranges. The first thing we need to do is to identify the cells we want to create named range. For an example look at the below image.
To arrive the profit in B4 cell I have applied the formula B2 – B3.
This is the common thing everybody does. But how about creating the Names and apply the formula something like “Sales” – “Cost”.
Place a cursor on the cell B2 > Go to Name box and call it as Sales.
Place a cursor on the B3 cell and call it Cost.
Now in the profit column, we can refer these names instead of cell references.
This is the basic thing about Named Ranges in Excel. Now we will see how to use these named ranges in VBA.
Create Named Ranges Using VBA Code in Excel
Have you ever thought of creating a named range using VBA Code? Follow the below steps to create named range in VBA.
Step 1: Define the variable as “Range”.
Sub NamedRanges_Example() Dim Rng As Range End Sub
Step 2: Now set the variable “Rng” to specific cells you wish to name.
Sub NamedRanges_Example() Dim Rng As Range Set Rng = Range("A2:A7") End Sub
Step 3: Using “ThisWorkbook” object access Names Property.
We have so many parameters with Names.Add method. Below are the explanations.
[Name]: Name is nothing but what is the name we would like to give to the range we specify.
While naming the cell it should not contain any special characters except underscore (_) symbol and it should not contain space characters as well, it should not start with numerical values.
[Refers to]: This is nothing but what is the range of cells we are referring to.
I think these two parameters are good enough to start off the proceedings.
Step 4: In the name, argument enters the name you wish to give. I have named as “SalesNumbers”.
Sub NamedRanges_Example() Dim Rng As Range Set Rng = Range("A2:A7") ThisWorkbook.Names.Add Name:="SalesNumbers" End Sub
Step 5: In the refers to argument enter the range of cells we wish to create. In the name of “Rng” variable we have already assigned the range of cells as A2 to A7, so supply the argument as “Rng”.
Sub NamedRanges_Example() Dim Rng As Range Set Rng = Range("A2:A7") ThisWorkbook.Names.Add Name:="SalesNumbers", RefersTo:=Rng End Sub
Ok, this VBA code will create named range for cells from A2 to A7.
Now in the worksheet, I have created some numbers from A2 to A7.
In the A8 cell, I want to have the total of the above cell numbers. Using named ranges in VBA, we will create a SUM of these numbers.
Sub NamedRanges_Example() Dim Rng As Range Set Rng = Range("A2:A7") ThisWorkbook.Names.Add Name:="SalesNumbers", RefersTo:=Rng Range("A8").Value = WorksheetFunction.Sum(Range("SalesNumbers")) End Sub
If you run this code manually or by pressing f5 key then, we will get the total of a named range in cell A8.
This is the basic must-know facts about “Named Ranges” in excel VBA.
Use Named Ranges in Excel VBA
In VBA using RANGE object, we refer to the cells we want to refer to. Similarly, we can also refer to those cells by using named ranges as well.
For example, in the above excel example, we have named the cell B2 as “Sales” and B3 as “Cost”.
By using actual cell reference we refer to those cells like this.
Sub NamedRanges() Range("B2").Select 'This will select the B2 cell Range("B3").Select 'This will select the B3 cell End Sub
Since we already created these cells we can refer to using those names like the below.
Sub NamedRanges() Range("Sales").Select 'This will select cell named as "Sales" i.e. B2 cell Range("Cost").Select 'This will select cell named as "Cost" i.e. B3 cell End Sub
Like this using VBA Named Ranges, we can make use of those cells. Using these named we can calculate the profit amount in cell B4. For this first name the cell B4 as Profit.
Now in the VBA editor apply this code.
Sub NamedRanges_Example1() Range("Profit").Value = Range("Sales") - Range("Cost") End Sub
This will calculate the profit amount in the cell named “Profit”.
This has been a guide to VBA Named Range. Here we discuss how to create and use VBA named ranges to selected cells or range of cells in excel along with practical examples and a downloadable template. Below you can find some useful excel VBA articles –