October 4, 2008

Referring To Named Ranges

Filed under: VBA — fatrunner @ 9:00 am

Excel’s VBA help file goes into referring to named ranges without much discussion on how to name a range.  Most people who have used excel for some time know how to name a range. On the off chance you don’t, one of the easiest ways is to select the area that will now be know by the ‘name’ and type that name in the name box which is located just to the left of the formula bar as shown by the arrow in the illustration below.

I’m certainly no expert in writing VBA code and get most of my coding done by recording macros. One thing I’ve noticed about Excel (at least in version 2003) is that when recording macros, it does not seem to like you naming ranges in this way.  So when I need to name a range while recording a macro I will name a range using another method. That way is by selecting the Insert menu and chosing Name –> Define.  The resulting dialog box allows you to name the range and even tweak it somewhat in the ‘refers to’ section of the dialog box. I haven’t ever had much of a need to tweak it, but I’ve seen some pretty cool stuff done to make the range dynamic in nature. Perhaps one day I’ll research that and do a post on it.  By defining a range in this way, Excel writes the following code:

ActiveWorkbook.Names.Add Name:="test", RefersToR1C1:="=Sheet1!R7C3:R17C4"

In this case I’ve used the name test to describe the range C7 through D17.  I’m certain there are plenty of other ways to define a range of cells, but for a beginner this is the easiest, plus you can let the macro do the heavy lifting for you.

Once you’ve defined a range it is easy later in the code to refer to that range by simply referring to the name instead of referring to the actual cells. For example if you wanted to return to the range to make it bold you could use the following code:

Application.Goto Reference:="test"
    Selection.Font.Bold = True

If the range you are referring to is in another workbook you can simply add the workbook name within the quotes like such:

Application.Goto Reference:="Workbook.xls!Test"

That’s pretty basic and doesn’t go beyond doing anything with the range once you’ve referred to it, but I’ll save that for another post.

Blog at WordPress.com.