Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. 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.
Written by Allen Wyatt (last updated October 7, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365
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:
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. 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:
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.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12612) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, 2021, and Excel in Microsoft 365. You can find a version of this tip for the older menu interface of Excel here: Using Named Ranges in a Macro.
Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!
When creating a macro to work with the names of defined ranges, you may need to know whether the scope of the name is for ...
Discover MoreMacros allow you to perform all sorts of file-related operations. One such operation allows you to delete a directory. ...
Discover MoreIf you have a series of values in a column, you might have a need to separate the values into even values and odd values. ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2023-11-02 10:05:49
J. Woolley
This Tip ignores the Worksheet.Names property.
See https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.names
Returns a Names collection that represents all the worksheet-specific names (names defined with the "WorksheetName!" prefix).
For more on this subject, see https://excelribbon.tips.net/T004633_Understanding_Scope_for_Named_Ranges.html
2023-11-01 13:36:11
J-M J
Another way to use named ranges is the [ ] notation. Here are two examples:
Range([DemPrev], [DemPrev].Offset(-1, 0)).Interior.Color = RGB(255, 255, 255)
OR
[DateRemplPrev].Value = jour
Short and efficient !
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.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2025 Sharon Parq Associates, Inc.
Comments