Excel: Xy_series Argument in NEW() Ignored with Chart Template

Last reviewed: November 30, 1994
Article ID: Q70217

The information in this article applies to:

Microsoft Excel for Windows, versions 3.0, 4.0, 4.0a

SUMMARY

When you use the NEW() function in conjunction with a chart template, it does not matter what you specify for the xy_series argument.

Microsoft Excel for Windows Versions 3.0

When you open a chart template and your data does not contain column field names, you will automatically be prompted with the First Row Contains dialog box regardless of the argument you specify for the xy_series in the NEW() function.

WORKAROUND

If you do not want the First Row Contains dialog box to appear, you can add a SEND.KEYS() statement immediately before the NEW() command, as in the following example:

   =SEND.KEYS("c~")
   =NEW("chart_template")

In this example, the SEND.KEYS() commnad selects the Category (X) Axis Labels option from the First Row Contains dialog box. Microsoft Excel then performs the NEW() macro command and displays the new chart created with the first row/column containing the category (x) axis labels. To use the SEND.KEYS() command to select the First Data Series option from the First Row Contains dialog box, use the following syntax:

   =SEND.KEYS("f~")

Microsoft Excel for Windows Version 4.0, 4.0a

In Microsoft Excel for Windows, versions 4.0 and 4.0a, when you use the NEW() function to open a chart template, Microsoft Excel does not display the New Chart Dialog Box at all. Microsoft Excel creates the chart based on the chart template type, and the selected data. The xy_series argument is ignored. If the chart template is an xy (scatter)chart, then the created chart is an xy (scatter). However, if the chart template is not an xy (scatter), then the selected data determines how the data is arranged in the chart.

In the following examples, the same chart template used with two different data selections result in different charts, even though the same macro command is used to create the charts. By default, if the first row or column contains text, this row or column is used for the category (x) axis labels.

Example 1

1. In a new worksheet, enter the following data:

      A1: Dec   B1: Jan   C1: Feb
      A2: 1     B2: 10    C2: 2
      A3: 2     B3: 20    C3: 4
      A4: 3     B4: 30    C4: 6

Select the range A1:C4 and run the following macro:

      =NEW("Chart_Template",2)
      =RETURN()

Because the first row contains text, the macro correctly uses the second argument and creates a chart that uses the first row of the selected data as the category (x) axis labels.

Example 2

In a new worksheet, enter the following data:

      A1: 1     B1: 10    C1: 2
      A2: 2     B2: 20    C2: 4
      A3: 3     B3: 30    C3: 6

Select the range A1:C3 and run the following macro:

      =NEW("Chart_Template",2)
      =RETURN()

The macro ignores the second argument and creates a chart that uses the first row of the selected data as the first data series. This occurs even if the chart template was created using the first row of data as the first data series.

WORKAROUND

To create a new chart with the first row of data used for the X-axis labels using data similar to that in Example 2, use the following macro:

   =COPY()
   =NEW("Chart_Template",2)
   =PASTE.SPECIAL(1,FALSE,TRUE,FALSE)
   =RETURN()

This macro copies the selected worksheet data, opens the chart template and pastes the data into the chart. The PASTE.SPECIAL() command specifies that the data series are in rows and that the first row is to be used for X-Axis labels.

NOTE: The arguments in the PASTE.SPECIAL() command above may need to be changed, depending on the how your data is laid out in the worksheet. For example, to specify that the data series are in columns and that the first column is to be used for X-Axis labels, use the following PASTE.SPECIAL() command:

   =PASTE.SPECIAL(2,FALSE,TRUE,FALSE)

MORE INFORMATION

The syntax of the NEW() function is as follows:

   NEW(type_num, xy_series, add_logical)

The xy_series argument allows you to designate four arguments where:

   0       Displays dialog box if selection is ambiguous.
   1       First row/column is the first data series.
   2       First row/column are the category (x) axis labels.
   3       First row/column are x-values; created chart is a
           scatter chart.

This argument is designed to allow you to choose how you want your data arranged in a chart.

REFERENCES

"Function Reference," version 4.0, pages 285-286, 318 "Function Reference," version 3.0, page 159, 178

KBCategory: kbusage
KBSubcategory:
Additional reference words: 3.00 4.00 4.00a

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: November 30, 1994
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.