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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 365 applications with VBA programming. Written in clear terms and understandable language, the book includes systematic tutorials and contains both intermediate and advanced content for experienced VB developers. Designed to be comprehensive, the book addresses not just one Office application, but the entire Office suite. Check out Mastering VBA for Microsoft Office 365 today!
When processing a worksheet with a macro, it may be helpful to periodically recalculate the worksheet. Wouldn't it be ...
Discover MoreSometimes, when you upgrade to a new version of Excel, you could run into a problem recording macros that you had no ...
Discover MoreHow Excel uses templates is different than how Word uses templates. This tip looks at those differences and discusses ...
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