Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Using Named Ranges in a Macro.

Using Named Ranges in a Macro

by Allen Wyatt
(last updated April 26, 2016)

12

Bruce has a named range (Account) defined in a workbook and he wonders how to access and use that named range from within a macro. There are several ways you can access the range, using either the Range object or the Names collection.

To access the named range using the Range object, all you need to do is provide the name of the range as a parameter to the object. This name is the same one that you defined within Excel. For instance, the following line could be used to change the interior color of the entire range:

Worksheets("Sheet1").Range("Account").Interior.Color = vbYellow

Note that the Range object is used relative to a particular worksheet, in this case Sheet1. You could also define a range object within VBA and then assign it to be equal to the named range, in this manner:

Set rng = Worksheets("Sheet1").Range("Account")

The other method of using the named range is to use the Names collection. The following line will again set the interior color of the range to yellow:

Workbooks("Book1.xls").Names("Account").RefersToRange.Interior.Color = vbYellow

Note that the Names collection is relative to the entire workbook, so it is not necessary to know which worksheet the named range is associated with when you use this method of access. You can also define a range object in VBA and assign it to be the same as the named range:

Set rng = Workbooks("Book1.xls").Names("Account").RefersToRange

You should know that the Names collection method of accessing a named range will only be viable if you don't have the same named range defined on different worksheets in the workbook. If you do, then you will need to use the Range object method, which requires the use of a specific worksheet name in the reference.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12612) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Using Named Ranges in a Macro.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Using the INFO Field

The INFO field allows you to include all sorts of information in your document. Here's a quick overview of the field and what ...

Discover More

Understanding Column Widths

Ever wonder why column widths are expressed in characters? The answer is rooted in history, as discussed in this tip.

Discover More

Creating Scenario Summaries

If you've defined a variety of scenarios for your workbook, Excel can provide a handy way to compare the effects of those ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (ribbon)

Replacing and Converting in a Macro

When you use a macro to process data you always run the risk of making that data unusable by Excel. This is especially true ...

Discover More

Turning Off Screen Updating

Want a quick way to speed up your macros? All you need to do is to stop Excel from updating the screen while the macro is ...

Discover More

Forcing Manual Calculation For a Workbook

If you have a large, complex workbook, you may want to make sure that it is always calculated manually instead of ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 8Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 7 - 0?

2016-04-27 09:21:20

jaker

Peg,
you can also highlight the text, copy and paste it into your favorite word processing app and then you can see everything.


2016-04-26 09:59:03

allen@sharonparq.com

Peg,

Sorry for the frustration. The only thing that is obscured by the ad is the word "vbYellow".

-Allen


2016-04-26 08:46:30

Bryan Marks

Hi, Peg,

If you hover over the ad, there is an option to click on the X and then select "Ad covers the text" to remove the ad from the page. Take it easy.


2016-04-26 08:32:31

Peg Molter

I am trying to read a very helpful hint, but the advertisement covers up vital information. http://excelribbon.tips.net/T012612_Using_Named_Ranges_in_a_Macro.html?awt_l=KmaKF&awt_m=IgSYPjOAtMQF4X

I realize you are forced to display adds to be able to do this, but when the add completely negates any usefulness of the tip, it pisses me off enough to say screw this site; I'll find my information somewhere where I can read ALL of it


2015-01-27 05:50:24

Willy Vanhaelen

@Matt:

Range("range_name").Address

If you use the Intersect method you can use range names as argument.


2015-01-27 00:56:25

Matt

Is it possible to determine the address of a named range?

I'd like to test a Workbook_SheetChange target against a list of named ranges.


2013-10-10 19:30:33

mfitzger@homestart.com.au

Beware using the [] constuct in conjunction with line continuation " _".

I use a lot of Excel tables and have used [TableName[ColName]] instead of Range("TableName[ColName]") extensively.

I have no problems with the former construct on a single line of code but if I use it in long commands that need a line continuation, the position of the line continuation can move unpredictably causing compile errors. For example, a line continuation entered thus:

[TableName[ColName]], _
[TableName[Col2Name]]

will change to:

[TableName[ColName] _
],[TableName[Col2Name]]

and throw a missing end bracket error!


2013-05-08 19:40:41

Aldo Santolla

Another powerful feature with Named Ranges in macro coding is the ability to limit your access to cells within the range group.

For example say you had on your sheet cells A2:C15 named as MySales (or whatever). This named range has 14 rows and 3 columns. You can access any cell within this range using .Cell() in macro code.

...

TheValue = [MySales].Cells(RowNum, ColNum).Value

TheCellAddress = [MySales].Cells(RowNum, ColNum).Address

TheFormula = [MySales].Cells(RowNum, ColNum).Formula

...

Using [ ] within code is a syntax shortcut for using the Range() property. All functions and calls that Range() has can be accessed using [ ] as well.

TotalRows = [MySales].Rows.Count
TotalCols = [MySales].Columns.Count
ColNum = [AB23].Column

...

The RowNum and ColNum are variables (you can use whatever you want) and they are limited to the Named Range size ... in the above example, RowNum is 1 to 14 and ColNum is 1 to 3. Any value outside these values with raise an error.

Happy coding :-)


2013-05-06 09:54:55

Don

Using named ranges is a very powerful piece of knowledge.

You mentioned that Names are scoped to the workbook. It is also possible to scope them to specific worksheets. I have developed a method for those reports that need to be refreshed each month/week/quarter, using the prior period's workbook as a basis.

All areas that need to be cleared are named "DataArea1", "DataArea2" etc and scoped to the worksheet. This allows me to copy a worksheet for one department for use as another and I don't have to track how many data areas I have already named.

The code for clearing the old data is

For each Worksheet in TgtWkbk
for each Name in Worksheet.Names
If Left(Name, 8) = "DataArea" then
Name.refers.Range.ClearContent
' Sometimes it is ClearAll if formatting should be reverted
end if
Next Name
Next WorkSheet

Additional, if the number of rows or columns in the data area needs to be changed, as with a report that lists the work days of a given month or the names of the employees in the department, using Range or Row Delete/Insert within the range resets the size of the range referred to by the Name.


2013-05-06 08:11:31

Bryan

Paul, this is one of the most useful tips I have ever seen on this site! Thanks for that.


2013-05-05 21:51:18

Paul Malycha

Another instance of using Square Braces.

Instead of using:
Workbooks("Book1.xls").Names("Account").RefersToRange.Interior.Color = vbYellow
This code can be used in place of the
[Account].Interior.Color = vbYellow
Much shorter to both write and read.


2013-05-05 21:44:05

Paul Malycha

I have found that the following method for useing Names in a macro is much easier to use if you have more that one sheet to refer to.
The follow is the Name "Account" used in one worksheet and the Name "Balance" in another worksheet using Square Braces to refer to the names.

...
cbNewBalance = [Account] & " : " & [Balance]
...

This method can be used effectively throughout any Module or Form in a Project.


This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.