title: Excel 2010 VBA ActiveChart.SetSourceData Failed
body: <p>I have a Excel VBA application which has been working fine in Excel 2003 but failed in Excel 2010.</p>
  
  <p>The relative codes are </p>
  
  <pre><code>Public Sub Create_Chart
      Dim c
      Dim OutputText As String
      OutputText = OutputSource
      Workbooks(NewWorkBook).Activate
  
      With ActiveSheet
          obj.Range(DataRange).NumberFormat = "0.0%"
          ActiveSheet.ChartObjects(1).Activate
          ActiveChart.ChartArea.Select
          ActiveChart.SetSourceData Source:=obj.Range(DataRange)
      End With
  End Sub
  </code></pre>
  
  <p>The error message appears when debugger hits ActiveChart.SetSourceData Source:=objNBR.Range(DataRange) with this window -
  <img src="https://i.stack.imgur.com/XkBNL.png" alt="enter image description here"></p>
  
  <p>The DataRange in debugger is F2:R2, F3:R3 with the obj pointing to the right Excel form -
  <img src="https://i.stack.imgur.com/7NLJN.png" alt="enter image description here"></p>
  
  <p>The the ActiveChart is from a template - 
  <img src="https://i.stack.imgur.com/xAHnK.png" alt="enter image description here"></p>
  
  <p>My debugger shows "ActiveChart.ChartArea.Select" is true.  And ActiveChart and obj are valid objects.  </p>
  
  <p>Most important is that Excel 2003 works fine.  I use the debugger to compare the Excel 2003 and 2010 obj and ActiveChart.  And I can not find much different in terms of codes.  I do see my template for the ActiveChart is a bit different in 2003 -
  <img src="https://i.stack.imgur.com/BR6fr.png" alt="enter image description here"></p>
  
  <p>The original template is exactly the same as shown in 2010 but it has been auto zeroed out during copied to the working sheet in 2003.  That is the only difference I can notice between 2003 and 2010.  I am not sure that can cause this error.</p>
  
  <p>I am not sure whether I have missed any evidence.  The error message does not give much details.  Is there any other ways to get more debug info?</p>
  
  <p>I appreciate it if anyone can help me find the problems and fixes.</p>
  
  <p>Thanks in advance</p>
  
  <p><strong>Edit1:</strong>  The obj is created in another routine and the DataRange are populeted in that routine -</p>
  
  <pre><code>Workbooks.Add
  MyWorkbook = ActiveWorkbook.Name
  Set obj = ActiveWorkbook.Worksheets(1)
  </code></pre>
  
  <p>And then the data is inserted to DataRange cells - F2:R2, F3:R3.  I can see the Excel file shown with the correct data.</p>
  
  <p><strong>Edit2</strong>  The Workbooks(NewWorkBook) in the above sub is generated from these codes and I can see the new Excel with the chart is shown on the screen -</p>
  
  <pre><code>Windows(Dir(TemplateFile)).Activate
  ActiveWorkbook.Sheets("C1").Select
  ActiveWorkbook.Sheets("C1").Copy
  NewWorkBook = ActiveWorkbook.Name
  Windows(NewWorkBook).Visible = True
  </code></pre>
  
  <p><strong>Edit3</strong> Use Sid's approach to declare the object first in this way -</p>
  
  <pre><code>Public Sub Create_Chart()
  
      Dim wb As Workbook
      Dim ws As Worksheet
      Dim objChrt As ChartObject
      Dim Chrt As chart
  
      Set wb = Workbooks(NewWorkBook)
      Set ws = wb.Sheets(1)
  
      Set objChrt = ws.ChartObjects(1)
      Set Chrt = objChrt.chart
  
      With wb
          obj.Range(DataRange).NumberFormat = "0.0%"
  
          'Chrt.Export ("c:\temp\testChrt.gif")
  
          'With obj.PageSetup
           '   .PrintArea = DataRange
           '   .Zoom = False
           '   .FitToPagesTall = 1
           '   .FitToPagesWide = 1
           '   .Orientation = xlLandscape
         ' End With
  
         ' obj.PrintOut Copies:=1
  
          Chrt.SetSourceData Source:=obj.Range(DataRange)
      End With
  End Sub
  </code></pre>
  
  <p>The error is exactly the same.  Please notice that I have the commented out codes to print out and save the Chrt and obj.Range(DataRange) objects.  The values are the same as image 2 and image 3 in the above.  So chart and data are there.  I just wonder why "Chrt.SetSourceData Source:=obj.Range(DataRange)" does not work in this case in 2010 but work in 2003.</p>