Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021
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, Excel in Microsoft 365, and 2021. 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, Excel in Microsoft 365, and 2021. You can find a version of this tip for the older menu interface of Excel here: Using Named Ranges in a Macro.
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!
If you need to limit the cells that are accessible by the user of a worksheet, VBA can come to the rescue. This doesn't ...
Discover MoreWrite out a check and you need to include the digits for the amount of the check and the value of the check written out ...
Discover MoreRenaming a worksheet within a macro is a relatively easy task. When you start renaming based on a range of names, though, ...
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