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:

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:

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

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.