How to refer Values from a Named Range – Excel VBA

.

Dear Readers,

I am writing this new article about How can you read values stored in a Name range in Excel Sheet in Excel Macro . For the readers who are unaware or need more information about the Name Range, I would suggest you to first go through the below mentioned article before jumping up to this article.
Click on this link to get to know more about “Name Range in Excel”.

In this article we will discuss on reading values stored in a Name range in Excel Sheet using Excel VBA. Name ranging in excel sheet means giving a name to a Range to refer it by the name given. Name can be given to a single cell or a range cells. So let’s see how can we access a name range in VBA.

Syntax:

You can refer a name range in VBA code like below:

Range(“<range name>”)

Above statement returns a 2 dimensional array which is holding all the values in the named range in (x, y) format where x = Row, y= Column.

Note: If named range is having only one cell then Range(“<range name>”) will return that one value. No need to put it in (x ,y) format.

Examples:

1. Named Range with a Single Cell

Name Range with single cell can result only one value therefore Range(“<range name>”) will return the value of that single cell.

Let’s take the same example which we took in my previous article to explain the Cell masking. Suppose you are creating a reporting tool to fetch data from Quality Center. To connect to Quality Center you are using some specific cells for users to enter User ID, Password, Domain and Project as shown in the below picture:

Name Range - Using Excel Macro

As you can see all the input cells are named like User ID Cell – G9 is named as qcID etc. Following Excel VBA code can be used to refer these named ranges:


qcURL = Range("qcURL") 'Referring to Named Range qcURL = Range("G7").Value
qcID = Range("qcID") 'Referring to Named Range qcID = Range("G9").Value
qcPassword = Range("qcPassword") 'Referring to Named Range qcPassword = Range("G11").Value
qcDomain = Range("qcDomain") 'Referring to Named Range qcDomain = Range("K9").Value
qcProject = Range("qcProject") 'Referring to Named Range qcProject = Range("K11").Value

Note: Since above Named ranges are having only cell hence it will return the single value without using it in 2 dimensional array format (x, y)

2. Named Range with with a Range having more than one cell

In the below picture you can see I have named the range A1:C3 as Named_Range_Multi

Named Range - With Multi-cells

In the above picture you can see, I have marked all the cell with the corresponding co-ordinates by which they can be referred. Below statements will refer all the cell values of Named_Range_Multi name range. Above name range has total 9 values in it. Using the below VBA code, I will show you how to refer them individually.

Range(“Named_Range_Multi”) will return a two dimensional array with total 9 values in it as shown in the above picture. To refer those values you can use the corresponding co-ordinates for respective cell value.


'First Row All columns
ValA1 = Range("Named_Range_Multi")(1, 1) 'This will return A1
ValB1 = Range("Named_Range_Multi")(1, 2) 'This will return B1
ValC1 = Range("Named_Range_Multi")(1, 3) 'This will return C1
'Second Row All columns
ValA2 = Range("Named_Range_Multi")(2, 1) 'This will return A2
ValB2 = Range("Named_Range_Multi")(2, 2) 'This will return B2
ValC2 = Range("Named_Range_Multi")(2, 3) 'This will return C2
'Third Row All columns
ValA3 = Range("Named_Range_Multi")(3, 1) 'This will return A3
ValB3 = Range("Named_Range_Multi")(3, 2) 'This will return B3
ValC3 = Range("Named_Range_Multi")(3, 3) 'This will return C3
 

 

Important: Reading Named Range Values using For Loop

As you know that you can read values from an array using for loop. So the above values from the named range Range(“Named_Range_Multi”) can be read using using loop as well.


Function Read_Named_Range
Dim Arr As Variant
Dim iRow As Integer
Dim iCol As Integer
Arr = Range("Named_Range_Multi")
For iRow = 1 To 3
    For iCol = 1 To 3
        MsgBox Arr(iRow, iCol)
    Next
Next
End Function

What is the benefit of referring a cell or range using Named Range?

Now there arises an obvious question that when I can refer a cell value directly by passing its address like Range(“G11”) then why to use Named range and what is the benefit of it?

From the above example where User ID , Password etc cells are given a name and the VBA code is referring to that name rather fix address like Range(“G12”) etc. It means, if you want to choose a different cell where you wants to put the User ID in other cell, then you have not to do any change in the code rather just provide the same name range to the new cell and VBA code will start referring to that cell.

This way code using Named Range has a flexibility to user to change the layout and cells in Excel sheet without any change in VBA code.

Isn’t this a great benefit??

It’s is always a good practice to refer a cell in VBA code with the Name range like in above example.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

2 Comments

  1. Denis

    I have a question : If I have a single column in my named range, could I use the formula like this? Range(“NameOfRange”)(1) to get the value of the first row of my single column? Or are we forced to use a double (1)(1) even though there’s only one column ?
    Thank you!

    Reply
  2. Denis

    In the same logic, if we use the code as follow : rng = Range(“NameOfRange”)(1:4) <– will the variable "rng" contain the range values of the 4 first rows ? (from first to fourth?).
    Thank you

    Reply

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest