Repeated calls of Chart.SetSourceData give error 1004

Error 1004: Repeated calls of Chart.SetSourceData

📊 Have you ever encountered the dreaded error 1004 when trying to update a chart in Excel? It's frustrating, right? But fear not! In this blog post, we will unravel the mystery behind this error and provide you with easy solutions to fix it. So grab your favorite beverage and let's dive in! 💪

The Context

📝 A fellow Excel enthusiast recently reached out to us with a problem. They had an application created in Excel 2003 that retrieves data from a source and updates a chart using the SetSourceData method in a VBA routine. Everything worked smoothly in Office 2003, but when they tried running the same application in Office 2010, they were greeted with the infamous error message:

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

🤔 Our friend suspected that this error might be related to the number of series in the chart, as the error seemed to occur sooner when more columns were passed in the range. They wondered if Office 2010 introduced some sort of mechanism or buffer for the chart object that didn't exist in Office 2003.

Reproducing the Error

🔍 To better understand the issue at hand, our friend provided us with a code snippet that could reproduce the error. Let's take a look at it:

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)

    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

🔎 By running the setDataChart subroutine provided, you'll be able to witness the error firsthand. It sets up a chart with randomly generated data and then repeatedly calls SetSourceData inside the sendData subroutine. Depending on the number of columns passed in the range, the error will occur sooner or later.

💡 Now that we have a clear understanding of the problem, let's explore some easy solutions!

Easy Solutions

1. Limit the number of columns: As observed, the error seems to be related to the number of series in the chart. One solution is to limit the number of columns passed in the range to reduce the likelihood of encountering the error. You can adjust the range in the SetSourceData line or modify the updateValues subroutine to generate fewer columns.

2. Delete existing series: Another workaround is to delete all the series before calling SetSourceData again. The code provided by our friend already includes an instruction to delete all the series using a For Each loop. Uncomment the line with On Error GoTo delSeries to enable the deletion of series before setting the source data again. Although this may add a minor overhead, it effectively mitigates the error.

3. Upgrade to a newer version of Excel: If you have the option, consider upgrading to a newer version of Excel, such as Office 365. These newer versions often include bug fixes and improvements that may resolve the error altogether.

Your Turn!

👩‍💻 Now that you're armed with these easy solutions, give them a try and see which one works best for you. Don't let error 1004 get in the way of your charting adventures! If you have any other Excel-related questions or need assistance, feel free to reach out to us. We're always here to help. 🤗

📢 Before you go, we'd love to hear from you! Have you encountered error 1004 in Excel before? How did you solve it? Share your experiences and insights in the comments below. Let's learn from each other and conquer Excel challenges together! 💪💼