Excel 2010 VBA ActiveChart.SetSourceData 失败

Excel 2010 VBA ActiveChart.SetSourceData Failed

提问by Don

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

我有一个 Excel VBA 应用程序，它在 Excel 2003 中运行良好，但在 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 -

当调试器使用此窗口命中 ActiveChart.SetSourceData Source:=objNBR.Range(DataRange) 时会出现错误消息 -

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

调试器中的 DataRange 是 F2:R2、F3:R3，其中 obj 指向正确的 Excel 表单 -

The the ActiveChart is from a template -

ActiveChart 来自模板 -

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

我的调试器显示“ActiveChart.ChartArea.Select”为真。ActiveChart 和 obj 是有效对象。

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 -

最重要的是 Excel 2003 工作正常。我使用调试器来比较 Excel 2003 和 2010 obj 和 ActiveChart。而且我在代码方面找不到太大的不同。我确实看到我的 ActiveChart 模板在 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.

原始模板与 2010 年显示的完全相同，但在 2003 年复制到工作表时已自动清零。这是我在 2003 年和 2010 年之间注意到的唯一差异。我不确定是否会导致此错误。

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 -

Edit1：obj 是在另一个例程中创建的，并且 DataRange 在该例程中填充 -

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.

然后将数据插入到 DataRange 单元格 - F2:R2、F3:R3。我可以看到显示正确数据的 Excel 文件。

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

Edit2 上面子中的 Workbooks(NewWorkBook) 是从这些代码生成的，我可以看到带有图表的新 Excel 显示在屏幕上 -

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

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

Edit3使用Sid的方法先这样声明对象——

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.

错误是完全一样的。请注意，我将注释掉的代码打印出来并保存 Chrt 和 obj.Range(DataRange) 对象。这些值与上面的图像 2 和图像 3 相同。所以图表和数据就在那里。我只是想知道为什么“Chrt.SetSourceData Source:=obj.Range(DataRange)”在 2010 年的这种情况下不起作用，但在 2003 年起作用。

回答by Siddharth Rout

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

除了上面的评论之外，这是您应该声明对象并使用它们的方式。你应该避免使用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

回答by whytheq

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

不是 100% 以下是完全相同的问题，但我觉得它很接近。

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

以下对我来说似乎是一个错误 - 为什么会ActiveChart支持一个属性，但引用同一图表的对象变量不支持该属性？

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:$BA"

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

回答by antonsachs

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

祝你好运