Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Defined Names

Defined names are an integral part of worksheet user interface design. Defined names are a superset of the more commonly understood named range feature. Defined names include named constants, named ranges and named formulas. Each type of defined name serves an important purpose and all nontrivial Excel worksheet user interfaces use some or all of the defined name types. The naming conventions used for the defined names demonstrated in this chapter are described in Chapter 3 Excel and VBA Development Best Practices.

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.

Figure 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 Practices.

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 Ranges

Named ranges enable you to reference a location on a worksheet with a friendly name that conveys information about that location, rather than using a range address that cannot be interpreted without following it back to the cell or cells it refers to. As the example below shows, named ranges also enable you to accomplish things 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 relative 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:

Column 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.

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.

Fully relative
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 not the only cell where the name can be used; it simply gives you a point from which to 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.

Figure 4-3 shows a simple table showing the sales for three hypothetical regions. The total sales for all three regions are calculated using the built-in SUM worksheet function, which you can see displayed in the formula 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 bottom of the list, the SUM function range did not adjust and the Total Sales number reported by the function is now wrong. This example was designed to make the problem blindingly obvious. In real-world worksheets, this type of mistake is frequent and rarely so obvious. In fact, it is one of the most common errors we discover when auditing malfunctioning 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 Insert > Name > Define to display the Define Name dialog (or better 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 function now display the correct answer, you can insert as many rows directly above it as you like and it will always sum the correct area. This feat can only be accomplished through the use of a fully relative named range. We use relative named ranges extensively in our sample application.

Named 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.Practical Example 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 Figure 4-1 to show a 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 4-7 shows this named formula.

Figure 4-7. A Dynamic Named Formula

[View full size 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

Defined names can have one of two scopes: worksheet level or workbook level. These are roughly analogous to private and public variables. Like variables, defined names should be given the most limited scope possible. Always use worksheet-level defined names unless you must make a name workbook level.Chapter 5 Function, General and Application-Specific Add-ins.

Some circumstances require you to use workbook-level defined names. Chapter 7 Using Class Modules to Create Objects, we demonstrate the use of a workbook-level defined constant to identify workbooks that belong to our application.