Excel 2010 VBA ActiveChart.SetSourceData Failed

Question

I have a Excel VBA application which has been working fine in Excel 2003 but failed in Excel 2010.

The relative codes are

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

The error message appears when debugger hits ActiveChart.SetSourceData Source:=objNBR.Range(DataRange) with this window -

The DataRange in debugger is F2:R2, F3:R3 with the obj pointing to the right Excel form -

The the ActiveChart is from a template -

My debugger shows "ActiveChart.ChartArea.Select" is true. And ActiveChart and obj are valid objects.

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 -

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.

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?

I appreciate it if anyone can help me find the problems and fixes.

Thanks in advance

Edit1: The obj is created in another routine and the DataRange are populeted in that routine -

Workbooks.Add
MyWorkbook = ActiveWorkbook.Name
Set obj = ActiveWorkbook.Worksheets(1)

And then the data is inserted to DataRange cells - F2:R2, F3:R3. I can see the Excel file shown with the correct data.

Edit2 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 -

Windows(Dir(TemplateFile)).Activate
ActiveWorkbook.Sheets("C1").Select
ActiveWorkbook.Sheets("C1").Copy
NewWorkBook = ActiveWorkbook.Name
Windows(NewWorkBook).Visible = True

Edit3 Use Sid's approach to declare the object first in this way -

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

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.

Answer 1

Further to the comments above, this is the way you should declare objects and work with them. You should avoid the use of Activesheet/Activeworkbook/ActiveChart... etc

You may also want to see THIS

This is just an example. Please amend it to suit your needs.

Public Sub Create_Chart()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim objChrt As ChartObject
    Dim Chrt As Chart

    Set wb = ThisWorkbook
    Set ws = wb.Sheets("Sheet1")

    Set objChrt = ws.ChartObjects(1)
    Set Chrt = objChrt.Chart

    Chrt.SetSourceData Source:=ws.Range("B2:B3,I2:I3")
End Sub

Answer 2

Not 100% that the following is exactly the same problem but I have a feeling it is close.

The following seems like a bug to me - why would ActiveChart support a property but an object variable referencing the same chart not support that property?

Sub findTheChart()

Dim p_Snapshot As Excel.Workbook
Dim myChartObject As Excel.ChartObject

Set p_Snapshot = Excel.Workbooks("theBookWithTheCharts")

Dim chtName As String
Dim dayNum As Integer
Dim sourceAddress As String
Dim ws As Excel.Worksheet
Dim r As Excel.Range

Set ws = p_Snapshot.Sheets("theSheetWithTheCharts")
sourceAddress = "$AW$69:$BA$84"

For Each myChartObject In ws.ChartObjects
    chtName = myChartObject.Name

    If (chtName = "Chart_nameGiven") Then
        myChartObject.Activate

        Set r = ws.Range(sourceAddress)
        'myChartObject.SetSourceData Source:=r '<<<<<<<<<<<<doesn't work
        Excel.ActiveChart.SetSourceData Source:=r '<<<<<<<<works fine!!!
    End If
Next myChartObject


End Sub

Answer 3

There is no need to declare extra variables. This works fine:

  With ThisWorkbook

  .Sheets(cstrParamTab).ChartObjects("IntradayChart1").Chart.SetSourceData _
    Source:=.Sheets(cstrChartBaseTab).Range(cstrColTimeStamp & clngTopRow & ":" & cstrColValueClose & plngLastRow), PlotBy:=xlColumns

  End With

The variables defining the tabs and range are predefined constants and vars. you can do it like this:

With ThisWorkbook

  .Sheets("Parameters").ChartObjects("IntradayChart1").Chart.SetSourceData _
    Source:=.Sheets("ChartBase").Range("B2:B239"), PlotBy:=xlColumns

  End With

but it's not going to work if your chart is dynamic.

Good luck