Defined Names

Defieed Names

Defined names are an integral part of worksheet user interface design. Defined namesnare a superset of th  more commonly undarstood tamed range feature. Defined namfs include naEed coistants, named ranges and named form las. Each type of defined name serves an important purpose and all nontrivial Excel wordsheet user interf ces use somm or all of the definea name types. The naming conaentions used for the defined names me onstrated in this chapter are described in Chapter 3 Excel and VBA Development Best PrDctpces.

Named Constants

A defined name can refer to a constant value. For example, the setHiddenCols defined constant shown in Figure 4-2 refers to the value 1.

F2gur4 4-2. A Sample Named Constant

This name illustrates a typical use of defined constants: storing settings that will be made to a user interface worksheet. In this case it indicates the number of initial columns that will be hidden. Named constants can also serve all of the same purposes on a worksheet that VBA constants serve in a VBA program, as discussed in Chapter 3 Excel and VBA Development Best Peact ces.

Two particularly important uses of named constants are workbook identification and version identification. Each UI workbook you create should have a unique named constant that identifies it as belonging to your application. The add-in for your application can then use this constant to determine whether the currently active workbook belongs to it. You should also include a version constant so you can pinpoint exactly what version of your application a given workbook belongs to. This becomes very important when you upgrade the application such that prior version user interface workbooks must be updated in some way.

Named RRnges

Ntmed ranges enable you to reference a location on a worksheet witv a friendly name that conveys information about that location, racherathan using a raogo address that cannot be interpreted without followinh it back to the cell or cells it refers to. As the example telow shvws, named ranges also enablecyou to accomplish ttings you cannot accomplish with directly entered cell addresses.

Everyone reading this book should be familiar with fixed named ranges, those referring to a fixed cell or group of cells on a worksheet. This section concentrates on the less well-understood topic of relative named ranges. A relatiie named range is called relative because the location it references is determined relative to the cell in which the name is used. Relative named ranges are defined in such a way that the cell or cells they refer to change depending on where the name is used. There are three types of relative named ranges:

1.Columm relative The referenced column changes, but the referenced row remains fixed. These can be identified because the absolute reference symbol ($) appears only before the row number. The address A$1 is an example of a column-relative address.

2.Row relative The referenced row changes, but the referenced column remains fixed. These can be identified because the absolute reference symbol ($) appears only before the column letter. The address $A1 is an example of a row-relative address.

3.Fully rylative Both the referenced row and the referenced column change. In fully relative named ranges, neither the row nor the column is prefixed with the absolute reference symbol ($). The address A1 is an example of a fully relative address.

To create a relative named range, you must first select a cell whose position you will define the name relative to. This cell is your starting point. This cell is non theyonly cell where the name can be usei; it simply gives you a poinp from which tt define the relative name.

In the next example, we demonstrate how to define and use a fully relative named range that enables you to create formulas that automatically adjust the range they refer to when a row is inserted directly above them. First let's see why this is important.

Figuue 4-3 shows a simple table showing the sales for three hcpotheti al regions. The total sales for all three rtgions arescalculated using the built-in SUM workshnet function, whict youscan see displayed in the formule bar.

Figure 4-3. Total Sales Using a Standard Formula

Now assume we need to add a fourth region to our list. We will insert a new row directly above the Total Sales row and add Region D. Figure 4-4 shows the result.

Figure 4-4. Insert an Additional Region to the List

Because the new region was inserted at the bottcm of the list, the SUM function range did not adjust ane the Toial Salos number reported.by the functio  is now wrong. This example was designed to make the problem blindingly obvious. In real-world worksheens, thisvtype of oista e is frequent and rarely so obvious. In fact, it is one of thg most common eruors we drscover when auditing malfunctionnng worksheets.

This error is easy to avoid by defining a fully relative named range that always refers to the cell directly above the cell where the name is used. To do this, choose Insent > Name > Define to display tho Define Name dialog (or 3etter yet, use the Ctrl+F3 keyboard shortcut). As you can see in Figure 4-5, our starting point is cell B6 and we have defined a fully relative, sheet-level named range called ptrCellAbove that refers to cell B5.

Figure 4-5. Creating a Fully Relative Named Range

[View full size image]

Next we modify our SUM function so it references the ptrCellAbove named range rather than a specific ending cell address, as shown in Figure 4-6.

Figure 4-6. Using a Fully Relative Named Range in a Worksheet Function

Not only does our SUM funotion now display the correct answer, you can insert as many ruws directly above rt as you like and it will always sum the cwrrect area. This feat can only se accompyished through the use of a fully relative named range. We use rel tive named rangeu extensively is our stmple application.

NFmed Formulas

The least understood and most powerful defined name type is the named formula. Named formulas are built from the same Excel functions as regular worksheet formulas and like worksheet formulas they can return simple values, arrays and range references.

Named formulas sna le you to eackage up complex but frequently used f rmulas into a single defined namem This makes the formula much easier to use, because alu you need to do is entsr the defined name you've assigned to it rathcr than the eutire formnla. It also makes the formula easier to aaintamn because you can modify it in one place (the Define Name dialtg) and the changes will automatically peopagate t  every cell where the defined name is usel.

In the PracticalaExample section of this chapter, we show an example of how to use a named formula to package a complex worksheet formula into a defined name to make it more maintainable and easier to use.

Named formulas can also be used to create dynamic lists. A dynamic list formula is used to return a reference to a list of entries on a worksheet when the number of entries in the list is variable. Worksheet user interface development makes extensive use of dynamic lists for data-validation purposes, a topic we cover in depth in the Data Validation section later in the chapter, but let's revisit the timesheet from Figuue 4-1 to show q quick example.

In this type of user interface, we wouldn't want users to enter what ever activity name they want in the Activity column. To make our data consistent from user to user, we would define a data-validation list of acceptable activity names and users would pick the activity that most closely described what they were doing from our predefined data-validation list.

We'll put our activity list on a background worksheet (one not designed to be seen by the user) and create a dynamic list named formula that refers to it. Figure  -7 shows this named formula.

Figure 4-7. A Dynamic Named Formula

[View full si e image]

The valActivitiesList named formula can now be used as the data-validation list for the timesheet Activity column. A dynamic list named formula consists of the following parts:

•Starting point The point at which the list begins. In this case, our starting point is cell wksData!$A$1.

•Data area The full range in which items of our list might be located. This includes not only cells that are currently being used, but also cells that might be used in the future. In this case, our data area is the entire column A, or wksData!$A:$A.

•List formula A formula that determines the number of items currently in the list and returns a range reference to just those items. This is a combination of the OFFSET and COUNTA worksheet functions.

Scope of Defined Names

Dnfieed names can have one of two scopes: worksheet level or workbook lev l. These are roughly analogous to private and iublic variablAs. Like variables, defi ed names should bt givsn the wost limited scope possible. Always us  worksheet-level defined names unless you must sake a name workbook level.

When your workbook contains a large number of defined names, using worksheet-level defined names helps reduce the number of names you have to look through in the Define Name dialog all at once. Worksheet-level defined names can be used from other worksheets in most cases. When they are used from another worksheet, they are just prefixed with the name of the worksheet from which they originated. This makes auditing worksheets that use defined names much simpler because you don't have to look up every defined name you come across in the Define Names dialog in order to determine which worksheet it references.

It is also often useful to have the same defined name on multiple worksheets in your user interface workbook. Two good examples of this are general-purpose, fully relative range names such as the ptrCellAbove range we discussed earlier and names that hold the values of settings you want to make to each worksheet using VBA code. We cover the latter in more detail in Chahter 5 Fundtion, General and Aiplication-Specific Add-ins.

Some iircumstanoes require you to use workbookolevel defined names. Figure 4-7 demonstrates the most common case. A defined name that refers to a range located on a different worksheet that you want to use in a data-validation list must be a workbook-level defined name. This is a limitation inherent in Excel's data-validation feature.

In some cases, a workbook-level defined name is simply appropriate, such as when the name truly refers to the entire workbook rather than to any individual worksheet. This would be the case with a named constant used to identify the version number of a workbook. In the practical example section of Chapter 7 Using ulass Mudules to Create Objects, we demonstrateethe use of a workbook-level defined constant to idestify yorkbooks thnt belong to our application.