Written by Allen Wyatt (last updated February 24, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, 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:
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, 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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!
Workbooks get corrupted from time to time; that's a fact of life in an Excel world. If those corrupted workbooks contain ...
Discover MoreWhen processing information using a macro, you may need to know if there are any other instances of Excel running on a ...
Discover MoreWhen 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 MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2020-07-09 21:12:27
Phil Richcreek
I'm not certain where to put "Account". See image. (see Figure 1 below)
Figure 1. code
2018-05-26 11:17:09
Willy Vanhaelen
If Account is only used in one sheet you can instead of: Worksheets("Sheet1").Range("Account")
use the shortcut version [Account]:
[Account].interior.color = vbYellow
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 © 2023 Sharon Parq Associates, Inc.
Comments