Repeated calls to 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 the source and updates the chart using SetSourceData in the VBA procedure, passing a range containing the cells in which the corresponding data is written.

The application works fine in Office 2003, but when the application runs in Office 2010, it causes this error:

Runtime Error '1004': Failed to execute the SetSourceData method of object_Chart.

I created a For loop in a simple Excel file in Office 2010, and depending on the number of columns passed in the range before the chart, the error will occur sooner or later. The more columns passed to Range, the sooner it will appear. I assume that this should be related to the number of series in the Chart (more columns more series).

Is this some kind of mechanism / buffer in an object or series of diagrams implemented in Office 2010 that was not in Office 2003? The same For loop never shows a problem when it is running in Office 2003, and I'm not sure how to solve this problem.

So far, I have been able to delete all the series that control the error using the Goto command, to delete all the series in the SeriesCollection series using the For Each loop, to select all the objects in the SeriesCollection set of the diagram. If I do this and resume the application, when I pass the range again, all the data will be correctly painted into the chart object.

An example for reproducing an error. The following code should be placed in the VBA module in a new Excel 2010 workbook. Run Sub setDataChart and the application will start before an error message appears.

  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 does not concern the cause of the error. This is a workaround.

Before calling SetSourceData delete the entire existing series that is currently on the chart, and the code will work as expected.

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

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

Answer 2

Another possibility is to define a named range for the data that is determined using the Offset formula and the corresponding reference cells. To do this, it is necessary that the data be contiguous, rather than changing the start row and the column in which it starts, and to set at least one reference formula ( =COUNTA() in the column / row containing the data) that can be used to set height / width of the offset range.

Otherwise, a very convenient little job is to get it out of the macros and put into the logic of the worksheet.