Bugs in Excel's Name Object
During development of the Name Manager, a number of bugs in Excel's defined name object have been revealed.
From the Name Manager's Manual:
There are some bugs in Excel’s Names collection and Name Object:
The RefersToLocal property of a name will not accept formula strings in Excel's UI language. For instance, in a Dutch Excel this code line:
Gives a runtime error, even if the formula is completely correct and identical to a worksheet formula. In the Name Manager a workaround has been used to overcome this problem. The RefersToLocal property ONLY accepts US syntax for formulas and separators.
Showing The "Define Name" Dialog Through VBA Code
When one has an Excel version that is NOT English and one shows the Insert Name dialog from Code, using either:
It always seems to show the dialog with the cell reference in R1C1 (of course Excel is set to A1)!
Names Local To Sheets With Special Characters In Their Name
When one defines a local name on a Sheet named "Test":
and renames this sheet to e.g. "Hi! There!" (without the quotes), this name becomes inaccessible for editing and deleting, yielding an invalid name error message. Other strange characters in sheet names (especially "[", "]" and char(3) ) may even cause a crash of Excel when trying to access these local sheet names. A check has been included in the Name Manager to overcome this problem.
Global And Local Names Sharing The Same Name
Accessing a global name whilst the active sheet has an identically named name local to that sheet, will change the properties of the local name and NOT the global name, even if the name is fully qualified with the workbook name. Name Manager bypasses this problem.
Names Who's RefersTo Property Starts With =!
Names with refers-to starting with =! (for example =!$A$1) may give incorrect results when calculation is called from VBA. Such names should (with this example) give you the result of the cell A1 on the sheet the name is used in, but when a recalc is forced through a macro, the name will get the result form the cell on the active sheet! Name manager will show a warning message whenever it finds a name of this type. If you want to have a name that will refer to a fixed cell on whatever sheet the name is used on, use =INDIRECT("A1") in stead of =!$A$1 to avoid this Excel bug.
Other issues with defined names
Using defined names to make your formulas easier to read has disadvantages too. Because normal cell references have been replaced with names, a spreadsheet may become harder to audit. Especially if hundreds of names have been applied, without any obvious naming convention, auditing such workbooks may become a daunting task.
Copying sheets can create links in names
When one copies a worksheet from one workbook to another, defined names may travel with the copy. All names that refer to the copied sheet directly are copied to the other workbook. But also all defined names used on the copied sheet are copied to the new workbook, but they keep referencing the original file. Thus, a link between the two books is created behind the scenes and mostly without the user realizing this has happened.
As long as one caters for these issues, defined names are an important part of any spreadsheet model and can improve the maintainability of the model too.