Listing Using the Names Object to List All Named Ranges

' Test the ListWorkbookNames procedure

' Outputs to cell A2 on the 2nd worksheet in the workbook Sub TestListNames()

ListWorkbookNames ThisWorkbook, ThisWorkbook.Worksheets(2).Range("a2") End Sub

Sub ListWorkbookNames(wb As Workbook, rgListStart As Range) Dim nm As Name

For Each nm In wb.Names

' print out the name of the range rgListStart.Value = nm.Name ' print out what the range refers to ' the ' is required so that Excel doesn't ' consider it as a formula rgListStart.0ffset(0, 1).Va1ue = "'" & nm.RefersTo ' set rgListStart to refer to the cell ' the next row down.

Set rgListStart = rgListStart.0ffset(1, 0)

Next End Sub

Figure 8.7

The output of the ListWorkbook-Names procedure lists all names, even those that are worksheet specific.

Figure 8.7

The output of the ListWorkbook-Names procedure lists all names, even those that are worksheet specific.

L ' MrtruiuFl Oilke CklcI Z003 Beta - Chapta I -.11 ■ i:i!'"..-. .

|l® Fie Edit tjew [nseit

Format Locfc Qafca Bindow Help

. fl x|

' --3 - " ^' — ' A

|U O 41 JIM

■ ;y witn L^aroes,,, End fteview., ■

AB - fi,









Refeis To


Sheetl! AName

=Sheet1 [$C$13





Sheet! !Te sting





1 1

r ~



|m 4 Îl-Hft SWl X£hBat2,- ||


On my test workbook, this procedure produces the output shown in Figure 8.7. Notice that every name in the workbook is listed. Well, I guess you'll have to take my word on that unless you open up the example workbook for this chapter. Anyway, the worksheet-specific names include the worksheet name as part of the name.

The ListWorkbookNames procedure works by looping through each name in the Names collection associated with the workbook passed to the procedure. Inside the loop, you print out the name in the cell referred to by the rgListStart variable. Then use the RefersTo property to display what the name refers to. Using the Offset property, print the value of the RefersTo property one cell to the right of rgListStart. Finally, adjust rgListStart to refer to the cell immediately below itself. Later in the chapter, you'll dig into the Offset property a little more.

You might think that you could return the value in a named range by using a statement such as the following:

' this statement doesn't work vRangeValue = ThisWorkbook.Names("TestName").Value

Although this does produce a value, it doesn't give you the value you might expect. Rather than giving you the value that currently occupies the cell referred to by TestName, it gives you the RefersTo value of the range name. In other words, ThisWorkbook.Names(x).Value is equivalent to ThisWork-book.Names(x).RefersTo. So how do you get the actual value of the cell? To do this, you need to use the RefersToRange property. RefersToRange returns an actual Range object (with its address equal to that given by the RefersTo property) that can be used like any other Range object.

Now that I've spent a good deal of time talking about the Names and Name objects, I should explain the other way in which you can refer to a named range. Basically, you can refer to these ranges much like any other range. For example, if you had a named range called "Testing" on a worksheet named "Sheet2", you could refer to this range with a statement such as the following:


I hear you. Why did I give you such an elaborate explanation on named ranges if you can refer to them so easily? Remember the difference between workbook-named ranges and worksheet-named ranges? Well, forget all of that if you use this method. The only way to refer to a workbook-named range in this manner is to also have a reference to the worksheet on which the name is defined. For example, say you have a workbook-named range called "Test" that is defined on "Sheetl". You cannot reference the Test named range from Sheet2. You can only reference Test from the worksheet object that it is defined on as shown in the following code snippet.

' This is legal

ThisWorkbook.Worksheets("Sheet1").Range("Test") ' This is not


I am not sure that all of this detail will make a lot of sense right now, or if it does, you may not grasp why I spend so much time on it. If you use named ranges and refer to them programmatically, however, I'd bet that at some point, the subtleties of using named ranges will cause you some grief.

0 0

Post a comment