Using Names

One of the most useful features in Excel is the ability to create names. You can create a name by selecting the Formulas tab on the Ribbon and clicking the Name Manager button to display the Name Manager dialog box, shown in Figure 5-1. If the name refers to a range, you can create it by selecting the range, typing the name into the Name box at the left side of the Formula bar, and pressing Enter. However, in Excel, names can refer to more than just ranges.

Name Manager

Name Manager

New... |

Edit,.. 1 1 Delete

1

Filter- 1

Name

Value

Refers To

5 cope

Comment .

'-.iData

mSrr,

=SheeBIKa:EÉÉ9

VArtScm

—i Database

^NameYQgEVSE...

=SheetI!5E54:5DS1Q

Workbook

Zl Dates

Date

^3heetl!5AS3

Sheetl

-' Fred

med';";."-;">

=Sheet3i5ES4!5ES

Woriibook

■ -j Input

{"Shelly'.^'.T^

=Sheet2! 5S 52; 5D Ç2

Workbook

—1 ItemsInA

=COUI\TTA{Eheetl!

Workbook

—1 John

John is an existing .

=3heet4l5Äil

Workbook

.11 Mary

["Mary ",",">

=Sheet3!5D5fi:5Fs6

Workbook

-Li MvName

{...>

={2,3, t; 3,4,5 rt, 5.5 ; 5,6,7; ó, 7,a ; 7,.,,

Workbook

1J Shelly

Shrily

=Sheet2!5Bs2

Sheetl

¡V

Refers to:

Figure 5-1

A name can contain a number, text, or a formula. Such a name has no visible location on the worksheet and can only be viewed in the Name Manager dialog box. Therefore, you can use names to store information in a workbook without having to place the data in a worksheet cell. Names can be declared hidden so they don't appear in the Name Manager dialog box. This can be a useful way to keep the stored information from being seen by users.

The normal use of names is to keep track of worksheet ranges. This is particularly useful for tables of data that vary in size. If you know that a certain name is used to define the range containing the data you want to work on, your VBA code can be much simpler than it might otherwise be. It is also relatively simple, given a few basic techniques, to change the definition of a name to allow for changes that you make to the tables in your code.

The Excel object model includes a Names collection and a Name object that can be used in VBA code. Names can be defined globally, at the workbook level, or they can be local, or worksheet-specific. The Name Manager dialog box indicates the level of a name under Scope. If you create local names, you can repeat the same name on more than one worksheet in the workbook. To make a Name object worksheet-specific, if you are entering it in the Name box, you precede its Name property with the name of the active worksheet and an exclamation mark. For example, you can type Sheet1!Costs to define a name Costs that is local to Sheet1, as shown in Figure 5-2.

Bjola - Micro soft Excel

_ ra

X

30 .-;

Heme Insert PageLaycul r-ortnüläs Data

üe'.'revu VreiM Develop

er '&> -

Sheetl! Costs)

Ji 100

»

A | B

C D I E

F | G

H 1

1

Jan Feb

Mar

1

Costs | 100

123 13ol

4

5

H

8 m Sheet 1 StieetZ

Bheeü -J

'¡XI Jlll-B

Average 117.6666667

CoLht: 3 Sum; 353 | Hg |Ü]

Ml • -

If you create the name using the New button in the Name Manager dialog box, you can select the scope of the name in the drop-down shown in Figure 5-3.

New Name

IL'0

Name! Scope; Comment:

Costs

Sheetl | XT

Workbook

Sheetl

SheetZ Sheet?

=Sheetl!5BS2:5D52

Pit I I Cancel

Pit I I Cancel

Figure 5-3

When you select a name using the Name box, you see the global names and those that are local to the active sheet. When you display the Name Manager dialog box, you see all the names in the workbook. The local names are identified by the worksheet name under Scope.

A great source of confusion with names is that they also have names. You need to distinguish between a Name object and the Name property of that object. The following code returns a reference to a Name object in the Names collection:

Names("Data")

If you want to change the Name property of a Name object, you use code like the following:

Names("Data").Name = "NewData" Having changed its Name property, you would now refer to this Name object as follows:

Names("NewData")

Global names and local names belong to the Names collection associated with the Workbook object. If you use a reference such as Application.Names or Names, you are referring to the Names collection for the active workbook. If you use a reference such as Workbooks("Data.xls").Names, you are referring to the Names collection for that specific workbook.

Local names, but not global names, also belong to the Names collection associated with the Worksheet object to which they are local. If you use a reference such as Worksheets("Sheet1").Names or ActiveSheet.Names, you are referring to the local Names collection for that worksheet.

There is also another way to refer to names that refer to ranges. You can use the Name property of the Range object. More on this later.

0 0

Post a comment