XL: Recorded Macro Fails to Insert New Data to Chart

ID: Q132968

The information in this article applies to:

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

SYMPTOMS

In the versions of Microsoft Excel listed above, if you record a macro to insert a non-contiguous, or non-adjacent, range of cells into a chart, you will receive the following error message when you run the macro:

Run-time error '1004'
Error in formula.

CAUSE

The error occurs because the complete reference, which includes the workbook and worksheet name, is recorded only for the first range of cells selected. The additional ranges specified in the macro do not contain the workbook and worksheet names necessary to make the macro work properly. For example, if you select cell A1, then hold down the CTRL key and select cells C1 and D1, the references are recorded as:

[Book2]Sheet1!R3C1,R3C2,R3C3

This problem occurs when you insert new data to a chart sheet or to an embedded chart on a worksheet other than the worksheet that contains the chart data.

RESOLUTION

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 professionals 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/overview/overview.asp

To avoid receiving a macro error when you run the recorded macro, modify it by adding the workbook and worksheet name to the cell references that are incomplete. For example, the following macro is recorded when you insert new data to an existing chart sheet:


   Sub Macro1()
       ActiveChart.SeriesCollection.Add Source:= _
           "[Book1]Sheet1!R2C1,R2C3,R2C5", Rowcol:=xlRows, _
           SeriesLabels:=False, CategoryLabels:=False, Replace:=False
   End Sub

For this macro to work properly, change the Source argument string as shown below in the following example:


   Sub Example1()
       ActiveChart.SeriesCollection.Add Source:= _
           "[Book1]Sheet1!R2C1,[Book1]Sheet1!R2C3,[Book1]Sheet1!R2C5", _
           Rowcol:=xlRows, SeriesLabels:=False, CategoryLabels:=False, _
           Replace:=False
   End Sub

Another way to modify the Source argument is to use a Range reference to refer to the range of cells you are inserting:


   Sub Example2()
       ActiveChart.SeriesCollection.Add Source:= _
           Workbooks("Book1").Worksheets("Sheet1").Range("A2,C2,E2"), _
           Rowcol:=xlRows, SeriesLabels:=False, CategoryLabels:=False, _
           Replace:=False
   End Sub

NOTE: In both of the examples shown above, the references to the workbook name are not necessary if the chart exists in the same workbook as the source data.

STATUS

Microsoft has confirmed this to be a problem in the versions of Microsoft Excel listed at the beginning of this article. This problem was corrected in Microsoft Excel 97 for Windows and Microsoft Excel 98 Macintosh Edition.

Additional query words: 7.00a 5.00a 5.00c XL5 XL7

Keywords : kbcode kbprg PgmOthr
Version : MACINTOSH:5.0,5.0a; WINDOWS:5.0,5.0c,7.0,7.0a
Platform : MACINTOSH WINDOWS
Issue type : kbbug