{"id":4103,"date":"2024-02-02T18:56:35","date_gmt":"2024-02-02T18:56:35","guid":{"rendered":"http:\/\/www.learnexcelmacro.com\/wp\/?p=4103"},"modified":"2024-02-02T18:56:35","modified_gmt":"2024-02-02T18:56:35","slug":"refer-name-range-values-excel-macro","status":"publish","type":"post","link":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/","title":{"rendered":"How to refer Values from a Named Range – Excel VBA"},"content":{"rendered":"

Dear Readers,<\/p>\n

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

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.\n<\/p>\n

Syntax:<\/h2>\n

You can refer a name range in VBA code like below:
\n<\/p>\n

Range(“<range name>”)<\/i><\/p>\n

<\/strong><\/p>\n

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.<\/p>\n

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.<\/i><\/p>\n

Examples:<\/h2>\n

1. Named Range with a Single Cell <\/h3>\n

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

Let’s take the same example which we took in my previous article to explain the Cell masking<\/a>. 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:<\/p>\n

\n\"Name\n<\/p>\n

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

\nqcURL = Range(\"qcURL\") 'Referring to Named Range qcURL = Range(\"G7\").Value\nqcID = Range(\"qcID\") 'Referring to Named Range qcID = Range(\"G9\").Value\nqcPassword = Range(\"qcPassword\") 'Referring to Named Range qcPassword = Range(\"G11\").Value\nqcDomain = Range(\"qcDomain\") 'Referring to Named Range qcDomain = Range(\"K9\").Value\nqcProject = Range(\"qcProject\") 'Referring to Named Range qcProject = Range(\"K11\").Value\n<\/code><\/pre>\n
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)<\/h3>\n
2. Named Range with with a Range having more than one cell <\/h3>\n
In the below picture you can see I have named the range A1:C3<\/strong> as Named_Range_Multi<\/i><\/strong><\/p>\n
\n\"Named\n<\/p>\n
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<\/i><\/strong> 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.<\/p>\n
\nRange(“Named_Range_Multi”)<\/i><\/strong> 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.\n<\/p><\/blockquote>\n
\n'First Row All columns\nValA1 = Range(\"Named_Range_Multi\")(1, 1) 'This will return A1\nValB1 = Range(\"Named_Range_Multi\")(1, 2) 'This will return B1\nValC1 = Range(\"Named_Range_Multi\")(1, 3) 'This will return C1\n'Second Row All columns\nValA2 = Range(\"Named_Range_Multi\")(2, 1) 'This will return A2\nValB2 = Range(\"Named_Range_Multi\")(2, 2) 'This will return B2\nValC2 = Range(\"Named_Range_Multi\")(2, 3) 'This will return C2\n'Third Row All columns\nValA3 = Range(\"Named_Range_Multi\")(3, 1) 'This will return A3\nValB3 = Range(\"Named_Range_Multi\")(3, 2) 'This will return B3\nValC3 = Range(\"Named_Range_Multi\")(3, 3) 'This will return C3\n \n<\/code><\/pre>\n
 <\/p>\n
Important: Reading Named Range Values using For Loop<\/h3>\n
As you know that you can read values from an array using for loop<\/a>. So the above values from the named range Range(“Named_Range_Multi”)<\/i><\/strong> can be read using using loop as well.<\/p>\n
\nFunction Read_Named_Range\nDim Arr As Variant\nDim iRow As Integer\nDim iCol As Integer\nArr = Range(\"Named_Range_Multi\")\nFor iRow = 1 To 3\n    For iCol = 1 To 3\n        MsgBox Arr(iRow, iCol)\n    Next\nNext\nEnd Function\n<\/code><\/pre>\n
What is the benefit of referring a cell or range using Named Range?<\/h2>\n
Now there arises an obvious question that when I can refer a cell value directly by passing its address like Range(“G11”)<\/i><\/strong> then why to use Named range and what is the benefit of it?<\/p>\n
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. <\/p>\n
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.<\/p>\n
Isn’t this a great benefit??<\/h3>\n
It’s is always a good practice to refer a cell in VBA code with the Name range like in above example.<\/p>\n<\/span>","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":45,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_et_pb_use_builder":"","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[1246,1674],"tags":[],"yoast_head":"\nHow to refer Values from a Named Range - Excel VBA - Let's excel in Excel<\/title>\n<meta name=\"description\" content=\"I am writing this new article about , How can you read values stored in a Name range in Excel Sheet using Excel VBA. See Excel Macro to read values from Name Range\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to refer Values from a Named Range - Excel VBA\" \/>\n<meta property=\"og:description\" content=\"I am writing this new article about , How can you read values stored in a Name range in Excel Sheet using Excel VBA. See Excel Macro to read values from Name Range\" \/>\n<meta property=\"og:url\" content=\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/\" \/>\n<meta property=\"og:site_name\" content=\"Let's excel in Excel\" \/>\n<meta property=\"article:publisher\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:author\" content=\"http:\/\/www.facebook.com\/vmlogger\" \/>\n<meta property=\"article:published_time\" content=\"2024-02-02T18:56:35+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Name-Range-Using-Excel-VBA.png\" \/>\n<meta name=\"author\" content=\"Vishwamitra Mishra\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@https:\/\/www.twitter.com\/learnexcelmacro\" \/>\n<meta name=\"twitter:site\" content=\"@learnexcelmacro\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Vishwamitra Mishra\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/\"},\"author\":{\"name\":\"Vishwamitra Mishra\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"headline\":\"How to refer Values from a Named Range – Excel VBA\",\"datePublished\":\"2024-02-02T18:56:35+00:00\",\"dateModified\":\"2024-02-02T18:56:35+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/\"},\"wordCount\":714,\"commentCount\":2,\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"articleSection\":[\"Excel Macro\",\"Excel Macro Basics\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/\",\"name\":\"How to refer Values from a Named Range - Excel VBA - Let's excel in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\"},\"datePublished\":\"2024-02-02T18:56:35+00:00\",\"dateModified\":\"2024-02-02T18:56:35+00:00\",\"description\":\"I am writing this new article about , How can you read values stored in a Name range in Excel Sheet using Excel VBA. See Excel Macro to read values from Name Range\",\"breadcrumb\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/vmlogger.com\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Excel Macro\",\"item\":\"https:\/\/vmlogger.com\/excel\/macro\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"How to refer Values from a Named Range – Excel VBA\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#website\",\"url\":\"https:\/\/vmlogger.com\/excel\/\",\"name\":\"Let's excel in Excel\",\"description\":\"Let's share knowledge\",\"publisher\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/vmlogger.com\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5\",\"name\":\"Vishwamitra Mishra\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"contentUrl\":\"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png\",\"width\":528,\"height\":560,\"caption\":\"Vishwamitra Mishra\"},\"logo\":{\"@id\":\"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/\"},\"description\":\"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.\",\"sameAs\":[\"http:\/\/www.learnexcelmacro.com\",\"http:\/\/www.facebook.com\/vmlogger\",\"https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro\",\"https:\/\/www.youtube.com\/c\/VMLogger\"],\"url\":\"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"How to refer Values from a Named Range - Excel VBA - Let's excel in Excel","description":"I am writing this new article about , How can you read values stored in a Name range in Excel Sheet using Excel VBA. See Excel Macro to read values from Name Range","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/","og_locale":"en_US","og_type":"article","og_title":"How to refer Values from a Named Range - Excel VBA","og_description":"I am writing this new article about , How can you read values stored in a Name range in Excel Sheet using Excel VBA. See Excel Macro to read values from Name Range","og_url":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/","og_site_name":"Let's excel in Excel","article_publisher":"http:\/\/www.facebook.com\/vmlogger","article_author":"http:\/\/www.facebook.com\/vmlogger","article_published_time":"2024-02-02T18:56:35+00:00","og_image":[{"url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2014\/06\/Name-Range-Using-Excel-VBA.png"}],"author":"Vishwamitra Mishra","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/www.twitter.com\/learnexcelmacro","twitter_site":"@learnexcelmacro","twitter_misc":{"Written by":"Vishwamitra Mishra","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#article","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/"},"author":{"name":"Vishwamitra Mishra","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"headline":"How to refer Values from a Named Range – Excel VBA","datePublished":"2024-02-02T18:56:35+00:00","dateModified":"2024-02-02T18:56:35+00:00","mainEntityOfPage":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/"},"wordCount":714,"commentCount":2,"publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"articleSection":["Excel Macro","Excel Macro Basics"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/","url":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/","name":"How to refer Values from a Named Range - Excel VBA - Let's excel in Excel","isPartOf":{"@id":"https:\/\/vmlogger.com\/excel\/#website"},"datePublished":"2024-02-02T18:56:35+00:00","dateModified":"2024-02-02T18:56:35+00:00","description":"I am writing this new article about , How can you read values stored in a Name range in Excel Sheet using Excel VBA. See Excel Macro to read values from Name Range","breadcrumb":{"@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/vmlogger.com\/excel\/2024\/02\/refer-name-range-values-excel-macro\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/vmlogger.com\/excel\/"},{"@type":"ListItem","position":2,"name":"Excel Macro","item":"https:\/\/vmlogger.com\/excel\/macro\/"},{"@type":"ListItem","position":3,"name":"How to refer Values from a Named Range – Excel VBA"}]},{"@type":"WebSite","@id":"https:\/\/vmlogger.com\/excel\/#website","url":"https:\/\/vmlogger.com\/excel\/","name":"Let's excel in Excel","description":"Let's share knowledge","publisher":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/vmlogger.com\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/7500a107b0b2d35a8492acf0d11fc8e5","name":"Vishwamitra Mishra","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/","url":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","contentUrl":"https:\/\/vmlogger.com\/excel\/wp-content\/uploads\/sites\/11\/2022\/07\/avataaars-1.png","width":528,"height":560,"caption":"Vishwamitra Mishra"},"logo":{"@id":"https:\/\/vmlogger.com\/excel\/#\/schema\/person\/image\/"},"description":"My name is Vishwamitra Mishra. Friends Call me Vishwa. I hold a Bachelor\u2019s Degree in Computer Science from D.A.V.V. Indore & currently working as a Technical Lead having over 7 years of experience.","sameAs":["http:\/\/www.learnexcelmacro.com","http:\/\/www.facebook.com\/vmlogger","https:\/\/twitter.com\/https:\/\/www.twitter.com\/learnexcelmacro","https:\/\/www.youtube.com\/c\/VMLogger"],"url":"https:\/\/vmlogger.com\/excel\/author\/vishwamitra\/"}]}},"_links":{"self":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4103"}],"collection":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/users\/45"}],"replies":[{"embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/comments?post=4103"}],"version-history":[{"count":0,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/posts\/4103\/revisions"}],"wp:attachment":[{"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/media?parent=4103"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/categories?post=4103"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/vmlogger.com\/excel\/wp-json\/wp\/v2\/tags?post=4103"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}