Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
When you define names in a workbook in Microsoft Excel, the workbook cannot
contain two or more defined names that differ from each by only the case of some or all of their letters. For example, you cannot create the following defined names in the same workbook:
Although the names use different combinations of uppercase and lowercase
letters, the letters in each name are all the same. Therefore, Microsoft
Excel considers these four names to be identical. Defining a name in a
workbook when another identical name (except for the case) already exists
results in the elimination of the original name. For example, if you define
the name "test" (without the quotation marks), Microsoft Excel eliminates
the name "Test" (without the quotation marks) from the workbook.
In Microsoft Excel, you can check the name of a defined name by using
Visual Basic for Applications macro code similar to the following:
MsgBox ThisWorkbook.Names(5).Name
MsgBox ThisWorkbook.Names("test").Name
In Microsoft Excel, if you specify a name within the parentheses in the
sample code, the
Name property returns a name that is identical (in terms of case) to the name that is defined in the
Define Name dialog box. In versions of Excel earlier than Excel 97, the Name property returns a name that is identical (in terms of case) to the name that you specify in the parentheses.
To demonstrate the difference in behavior, run the following subroutine:
Sub TestName()
MyArray = Array("test", "Test", "tEST", "TEST")
For Each xName In MyArray
ThisWorkbook.Names.Add Name:=xName, RefersTo:="5"
MsgBox ThisWorkbook.Names("test").Name
Next xName
End Sub
The message boxes display different values, depending on the version of
Microsoft Excel that you are using.
MsgBox Value in MsgBox Value in
Defined name Microsoft Excel 97, 2000 Microsoft Excel 5.0, 7.0
------------------------------------------------------------------
test test test
Test Test test
tEST tEST test
TEST TEST test
This change in behavior may cause a problem if you compare the name that is
returned by a Name property to a string. For example, although the
following code always works in versions of Excel earlier than Excel 97, it may not work in the current version of Microsoft Excel:
MsgBox ThisWorkbook.Names("test").Name = "test"
You can prevent problems from occurring by standardizing the case of the
name before you compare it. For example, the following code works correctly
in any version of Microsoft Excel:
MsgBox UCase(ThisWorkbook.Names("test").Name) = UCase("test")