Excel.Tips.Net ExcelTips (Menu Interface)

Using Named Ranges in a Macro

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

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 (3106) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Using Named Ranges in a Macro.

Related Tips:

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!


Leave your own comment:

  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*What is 5+3 (To prevent automated submissions and spam.)
           Commenting Terms

Comments for this tip:

Randy Downs    28 Dec 2016, 21:06
Nice reference. I ended up using ThisWorkbook since my macro ran within the active workbook.
Yann    21 Jul 2016, 05:19

I am trying to use a name range within the consolidate function but I get an error 1004.

Sub consolidate()
Dim conso As Range

Selection.Offset(2, 2).Select
Set conso = Selection

LastRow = Cells(Cells.Rows.Count, "C").End(xlUp).Row + 5
Range("C" & LastRow).Select

Selection.consolidate Sources:= _
    Range("conso"), Function:= _
        xlSum, TopRow:=False, LeftColumn:=True, CreateLinks:=False

End Sub '

I have tried to search online but couldn't find any info on the consolidate function and naming a range in the function.
Any thoughts?
Florian    15 Apr 2016, 10:19
I am trying to retrieve the column number of a named range in a sheet, using a concatenation of two string object variable Raw_point and Raw_list_name, like this:

Run_value_column = Run_wb.Names(Raw_point & "_" & Raw_list_name).RefersToRange.column

Unfortunately the code does not work(Application-defined or object-defined error), although it does when I refer to the named range directly between "". Is there a way to use Names() with object variables?
Thank you.


S One    26 Mar 2016, 10:50
Just to confrim, I use excel 2003 and have found this site very useful.
Reza    02 Sep 2015, 05:39
When I set the range from another sheet that isn't active vba gives an error and macro doesn't proceed until I select that sheet.
How can I do that? Is it Possible
awyatt    16 Mar 2015, 15:23

But people still use the old versions. That's why this site exists.

If you want tips for the newer versions (including the ones you noted), click the links on this page that lead to those tips--there are plenty of them here!

Scott Lothrop    16 Mar 2015, 15:13
It's time to update your tips, current versions of Excel are 2010 and 2013. None of the versions you list are even supported.
Bob Thing    02 Dec 2014, 15:31
The active workbook is ActiveWorkbook, so ActiveWorkbook.Names("Account") will access the named range "Account" in the current workbook.

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives


Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2017 Sharon Parq Associates, Inc.