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

MORE INFORMATION

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"

REFERENCES

"User's Guide," version 5.0, pages 132, 136, 144

KBCategory: kbdocerr kbtlc KBSubcategory: Additional reference words: 5.00 5.00a 5.00c 7.00 define.name Keywords : kbdocerr kbtlc Version : 5.00 5.00c 7.00 | 5.00 5.00a Platform : MACINTOSH WINDOWS 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 27, 1997 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.