Repeated calls of Chart.SetSourceData give error 1004

Question

I have a problem with an application that was created in Excel 2003 in my company. The application retrieves data from a source and updates a Chart using the SetSourceData in a VBA routine passing a Range containing the cells where the relevant data is written.

The application runs just fine in Office 2003, but when the application is executed in Office 2010 it gives this error:

Run-time error '1004': Method 'SetSourceData' of object'_Chart' failed.

I have created a For loop in a simple Excel file in Office 2010 and depending on the number of columns passed in the Range to the Chart the error will come up sooner or later. The more columns passed in the Range the sooner it will come up. I guess this has to be related with the number of series in the Chart(more columns more series).

Is this some sort of mechanism/buffer in the Chart Object or Series implemented in Office 2010 that did not exist in Office 2003? The same For loop never shows a problem when it is run in Office 2003 and I am not sure how to solve this problem.

So far I have only been able to delete all the Series controlling the Error with a Goto instruction to delete all the series in the SeriesCollection using a For Each loop to select all the objects in the SeriesCollection of the Chart. If I do this and resume the execution of the application when I pass the Range again all the data is painted in the Chart Object properly.

Example to reproduce the error. The following code is to be put in a VBA module in a new Excel 2010 workbook. Run the Sub setDataChart and the application will run until the error message is displayed.

    Sub setDataChart()
    Call createAColValues
    ActiveSheet.Shapes.AddChart.Select
    ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
    ActiveChart.SetSourceData Source:=Range("A1:FA6"), PlotBy:=xlColumns
    ActiveSheet.ChartObjects(1).Activate
    With ActiveChart.Parent
         .Height = 325
         .Width = 900
         .Top = 120
         .Left = 10
    End With
    Call updateValues
    Call sendData
End Sub

    Sub sendData()
    Dim cht As ChartObject
    Set cht = ActiveSheet.ChartObjects(1)

    'On Error GoTo delSeries:
    For i = 0 To 1000
        cht.Chart.SetSourceData Source:=ActiveSheet.Range("A1:FA6"), PlotBy:=xlColumns
    Next i
End Sub

Sub createAColValues()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("A1:A2").Select
    Selection.AutoFill Destination:=Range("A1:A6"), Type:=xlFillDefault
    Range("A1:A6").Select
End Sub

Sub updateValues()
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=RANDBETWEEN(0,10)"
    Range("B1").Select
    Selection.AutoFill Destination:=Range("B1:B6"), Type:=xlFillDefault
    Range("B1:B6").Select
    Selection.AutoFill Destination:=Range("B1:FA6"), Type:=xlFillDefault
    Range("B1:FA6").Select
End Sub

Answer 1

This doesn't address why the error occurs. This is a workaround.

Before calling SetSourceData, delete all the existing series currently in the chart, and the code will run as expected.

For j = cht.Chart.SeriesCollection.Count To 1 Step -1 
    cht.Chart.SeriesCollection(j).Delete
Next j

I'm not sure why the error occurs in the first place, but this makes it go away.

Answer 2

Another possibility is to define a named range for the data that's defined using the Offset formula and appropriate reference cells. This requires the data to be contiguous, not change the initial row & column it begins in, and for you to setup at least one reference formula (=COUNTA() on the column/row containing the data) that can be used to set the height/width of the offset range.

Otherwise a very handy little work around to take this out of macros and put it in worksheet logic.