Microsoft KB Archive/105770

= Microsoft Knowledge Base =

XL: Cannot Use Name Box to Define 3-D Name
Last reviewed: March 27, 1997

Article ID: Q105770

5.00 5.00c 7.00 | 5.00 5.00a

WINDOWS        | MACINTOSH kbdocerr kbtlc

The information in this article applies to:


 * Microsoft Excel for Windows, versions 5.0, 5.0c
 * Microsoft Excel for Windows 95, version 7.0
 * Microsoft Excel for the Macintosh, versions 5.0, 5.0a
 * Microsoft Excel for Windows NT, version 5.0

SUMMARY
Page 144 of the "User's Guide," version 5.0, states that you can define a name with a 3-D reference. While this information is correct, note that you cannot use the name box (on the Formula bar) to define a 3-D name.

WORKAROUND
To define a 3-D name, specify the 3-D reference in the Refers To box in the Define Name dialog box (from the Insert menu, choose Name, and choose Define).

To manually create a 3-D name
The following example assumes you have a workbook that contains five worksheets: Sheet1, Sheet2, Sheet3, Sheet4, and Sheet5, appearing in that order.

To create a 3-D defined name "TestRange" that refers to the range $A$1:$D$10 on sheets Sheet1 through Sheet4:


 * 1) With a single worksheet tab selected, choose Define from the Insert menu, and choose Name.
 * 2) In the Names In Workbook box, type "TestRange" (without the quotation marks).
 * 3) In the Refers To box, type "=Sheet1:Sheet4!$A$1:$D$10" (without the quotation marks).
 * 4) Choose OK.

To create a 3-D name with a Visual Basic procedure
Microsoft provides examples of Visual Basic procedures 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 Visual Basic procedure is provided "as is" and Microsoft does not guarantee that it can be used in all situations. Microsoft does not support modifications of this procedure to suit customer requirements for a particular purpose. Note that a line that is preceded by an apostrophe introduces a comment in the code--comments are provided to explain what the code is doing at a particular point in the procedure. Note also that an underscore character (_) indicates that code continues from one line to the next. You can type lines that contain this character as one logical line or you can divide the lines of code and include the line- continuation character. For more information about Visual Basic for Applications programming style, see the "Programming Style in This Manual" section in the "Document Conventions" section of the "Visual Basic User's Guide."

To create the 3-D name "TestRange" in Visual Basic, you could use a procedure similar to the following:

' Enter the following two lines of code as one single line and remove the ' underline character: ActiveWorkbook.Names.Add Name:="TestRange", _

RefersToR1C1:="=Sheet1:Sheet4!R1C1:R10C4"