The Excel Insert | Name | Paste command has an option, Paste List, which allows you to place on a worksheet a list of the names that have been defined in a workbook.
OAK takes this idea and radically expands on it. In addition to displaying the title and definitions of the names in a workbook, the OAK Build Name Database provides information on the properties of the names. These increase the chances of finding mistakes in them.
Why would you want to use it?
A database of names provides the means to spot a variety of mistakes that are not otherwise easily detected. The benefits of this functionality are listed as follows:
| • | The names database shows all of the names in a workbook at once; in contrast, Paste List will only show the global names and those local names that are local to the currently active worksheet. |
| • | The names database allows you to see that local names that are intended to be repeated on several worksheets do in fact appear consistently on all of them. |
| • | If you create a name on one worksheet that already exists on another, the new name will not replace the old one. There will instead be two names in the workbook, one global and one local to the worksheet on which it is located. These ghost names show up in the name database. |
| • | Names do not have to refer to specific cell references or ranges; instead they can refer to constants, relative ranges or formulas. These names are flagged in the name database. |
| • | If you have named the entries in a 20-year profit and loss statement, then the names should each be 20 columns wide. If some are 21 and others 19, you may have highlighted one column too many or too few when you defined them. The width of each named range is shown in the name database. |
| • | It is possible for two or more names to be defined so that they refer to the same area. Normally this is a mistake, resulting from forgetting that something has already been given a name and naming it a second time. The duplicated names show up in the “overlap report” if that option of the name database is checked. |
Output
The Names Database collects the following information about every name in the active workbook:
| • | Full Name. The name, including the sheet specifiers for local names (this is in the hidden column A); |
| • | Short Name. This is the same as the long name, except that sheet specifiers are not included for names; |
| • | Defined on Sheet. This lists the sheet that the name is defined on. This is blank for global names and is simply the sheet specifier for local names; |
| • | Formula. The range, constant or formula that the name refers to (this is blank for names that refer to relative ranges); |
| • | Refers to Sheet. Specifies the sheet that the name refers to (flags in this column indicate constant, formula, relative, and error names, as well as those linked to another workbook); and, |
| • | Address. The absolute address that the name refers to. |
The following additional information is also included, however these are most useful for names that refer to ranges:
| • | Areas: The number of independent, non-contiguous ranges that the name refers to; |
| • | Top: The topmost row of the range that the name refers to; |
| • | Bottom: Bottommost row of the range that the name refers to; |
| • | Height: The number of rows in the range that the name refers to; |
| • | Left: The leftmost column of the range that the name refers to; |
| • | Right: The rightmost column of the range that the name refers to; and |
| • | Width: The number of columns in the range that the range refers to. |
The Data | Filter | AutoFilter option is, by default, activated on the output worksheet. This enables you to easily filter the Names Database for a specific criterion in every column. This is especially useful when your workbook contains a large number of names.
How to use it
Open a workbook, then select the OAK4 | Names | Build Name Database command.
A dialog box similar to the one shown above will be displayed. From this, you can specify the following options:
| • | produce a report listing overlapping named ranges; |
| • | exclude special names from the overlap test; |
| • | place the resulting reports either on a new worksheet in the active workbook, or in a new workbook; and, |
| • | check for hidden names. |
Finding Overlapping Range Names
If you wish to produce a report listing overlapping named ranges, it is worth noting that some names, particularly “Print_Area” and “Print_Titles”, will tend to lead to a great number of spurious overlaps. These names are required for print formatting and, as such, do not alter the functionality of the model. As they will most likely cover a significant area of any worksheet on which they are defined, these names can be expected to overlap with many of the other names that are used for calculations.
To prevent such “deliberate” name overlaps from being reported, you should choose the “Exclude Special Names from Overlap Test” option. Any additional names, which will result in overlap fails that you deem to be inconsequential, can also be excluded from the Overlap Test by simply adding it to the list of Special Names using the Add button on the Name Database Options dialog box. Likewise, if you decide that a name should no longer be exempt from the Overlap Test, then this name can be deleted from the Special Names list by using the Remove button.
The results of the Overlap Test are compiled into a separate worksheet, next to the Names Database. This report shows the identity and range of the intersecting names, as well as the cell in which they cell(s) in which they overlap.
This is a powerful tool for identifying obsolete names still present in the workbook that should be deleted.
Check for hidden names
One of the options available to you when building up the Names Database is the decision of whether to include hidden names in the database or to omit them completely.
It should be noted that the hidden names that this option deals with are those that have been hidden from the worksheet user at the macro-level i.e. by altering the “Visibility” property of the name from within visual basic. To clarify, the Check for hidden names option does not describe a check for names that occur in hidden cells on the active worksheet or hidden worksheets. Such names will always be detected, irrespective of whether the Check for hidden names option is selected or not.
Wrinkles
If two names refer to overlapping ranges, they appear twice in an overlap report, once in one sequence, the other time in the reverse order.
Some versions of Excel allow Natural Language Formulas, in which calculations can be expressed in terms of the labels that are near to the input assumptions. The feature was introduced in Excel 97 but has been dropped from Excel 2007 as it turned out to be a poor idea.
The labels behave somewhat like names, but they are not names. As a consequence, they can cause confusing interactions with the Build Name Database command If the Build Name Database command is run on a workbook that uses labels in its formulas, the following warning will be issued at the top of the Name Database report: “Warning: The 'Accept Labels in Formulas' option is ON!”. We recommend that you turn this feature off within Excel 2003, using the Excel | Tools | Options | Calculation command.
What you could do if you didn't have OAK
As alluded to at the beginning of this section, Excel’s Paste List option offers the fundamental name storing capabilities similar to those provided by OAK’s Names Database. However, the difference lies in the richness of the information. The information provided about each name when using the Excel Paste List option is restricted to the identity of the name and its range location. By comparison, OAK’s Name Database option provides that information, in addition to information regarding the range location, size and type.