XL97: Defined Names Are Case Sensitive in Microsoft Excel 97

Last reviewed: March 13, 1998
Article ID: Q160327

The information in this article applies to:

Microsoft Excel 97 for Windows

SUMMARY

When you run a Visual Basic for Applications macro in Microsoft Excel 97, and your macro code reads the Name property of an item in the Names collection, the result may be slightly different than the result you receive in earlier versions of Microsoft Excel. This behavior occurs because defined names are case sensitive in Microsoft Excel 97.

This article explains the new behavior and the problems it may cause.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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:

   Test
   test
   tEST
   TEST

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 97, 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 earlier versions of Microsoft Excel, 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   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 earlier versions of Microsoft Excel, it may not work in Microsoft Excel 97:

   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")

Additional query words: XL97
Keywords : xlvbahowto xlvbainfo
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 13, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.