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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
When you change from one worksheet to another, you may want to have Excel automatically run a macro for the worksheet you ...
Discover MoreMacros are great at working with text. This tip presents an example that shows this versatility by reversing the contents ...
Discover MoreIf you use Excel to create a macro-based application, you may want to make sure that your programs cease working after a ...
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 © 2024 Sharon Parq Associates, Inc.
Comments