Figure

You create workbook-named ranges by entering a name in the Name drop-down and pressing Enter.

Figure 8.4

To create a worksheet-named range, you must specify the name of the worksheet in single quotes followed by an exclamation point.

Figure 8.4

To create a worksheet-named range, you must specify the name of the worksheet in single quotes followed by an exclamation point.

Figure 8.5

Worksheet-named ranges list the name of the worksheet on the right-hand side of the dialog box.

Figure 8.5

Worksheet-named ranges list the name of the worksheet on the right-hand side of the dialog box.

In order to refer to a named range programmatically, you need to know whether the named range is a workbook-named range or a worksheet-named range. Then, to access the named range, you use the Names object associated with the Workbook object for workbook-named ranges or the Worksheet object for worksheet-named ranges. If you didn't set up the named ranges, you can easily inventory all of the defined named ranges by viewing the Define Name dialog box for each worksheet in a given workbook. To view this dialog box, select Insert ^ Name ^ Define from the Excel menu.

Using this method of viewing named ranges, you must take into account the fact that only worksheet-named ranges applicable to the current worksheet are displayed. In order to see all of the names in a workbook, you need to visit each worksheet and display this dialog box. In Figure 8.5 you can see that it is easy to distinguish between workbook- and worksheet-named ranges when viewing names in a workbook. Worksheet-named ranges are distinguished by the inclusion of the worksheet name to the right of the named range. In Figure 8.6 I have selected a different worksheet from the one that was active for Figure 8.5. As you can see, both worksheets have a worksheet-named range called Testing.

Figure 8.6

Only worksheet-named ranges applicable to the current worksheet are displayed.

Figure 8.6

Only worksheet-named ranges applicable to the current worksheet are displayed.

Of course, you can use a much better way to do this programmatically—check out Listing 8.4. Here, the ListWorkbookNames procedure prints out all names in the workbook, including all worksheet-specific names.

0 0

Post a comment