Excel 2010 VBA ActiveChart.SetSourceData失败 [英] Excel 2010 VBA ActiveChart.SetSourceData Failed

问题描述

相关代码是

  Public Sub Create_Chart 
 Dim c 
 Dim OutputText As String 
 OutputText = OutputSource 
工作簿（NewWorkBook）.Activate 
 
使用ActiveSheet 
 obj.Range（DataRange）.NumberFormat =0.0％
 ActiveSheet.ChartObjects（1）.Activate 
 ActiveChart.ChartArea.Select 
 ActiveChart.SetSourceData来源：= obj.Range（DataRange）
结束
 End Sub 
  

当调试器使用此窗口击中ActiveChart.SetSourceData Source：= objNBR.Range（DataRange）时出现错误消息 -

调试器中的DataRange是F2：R2，F3：R3，对象指向右边的Excel窗体 -

ActiveChart来自模板 -

我的调试器显示ActiveChart.ChartArea.Select是true。 ActiveChart和obj是有效的对象。

最重要的是Excel 2003工作正常。我使用调试器比较Excel 2003和2010 obj和ActiveChart。我在代码方面找不到很大的不同。我看到我的ActiveChart模板在2003年有点不同 -

原始模板与2010年所示相同，但​​在2003年复制到工作表中已被自动归零。这是我可以注意到的唯一区别2003年和2010年，我不确定是否可能导致此错误。

我不知道我是否错过了任何证据。错误信息不会给出很多细节。有没有其他方法可以获得更多的调试信息？

如果有人可以帮助我找到问题和修复程序，我很感激。

提前感谢

Edit1：在另一个例程中创建obj，并在该例程中弹出DataRange - p>

  Workbooks.Add 
 MyWorkbook = ActiveWorkbook.Name 
设置obj = ActiveWorkbook.Worksheets（1）
  

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

Edit2 上述子工作簿（NewWorkBook）是从这些代码生成的，我可以看到新的Excel与图表显示在屏幕上 -

  Windows（Dir（TemplateFile））。激活
 ActiveWorkbook.Sheets（C1）。选择
 ActiveWorkbook.Sheets（C1）。复制
 NewWorkBook = ActiveWorkbook.Name 
 Windows（NewWorkBook）.Visible = True 
  

Edit3 使用Sid的方法以这种方式首先声明对象 -

  Public Sub Create_Chart（）
 
 Dim wb As Workbook 
 Dim ws As Worksheet 
 Dim objChrt As ChartObject 
 Dim Chrt As chart 
 
设置wb =工作簿（NewWorkBook）
设置ws = wb.Sheets（1）
 
设置objChrt = ws.ChartObjects（1）
 Set Chrt = objChrt.chart 
 
带有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副本：= 1 
 
 Chrt.SetSourceData来源：= obj.Range（DataRange）
结束
 End Sub 
  

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

解决方案

没有必要声明额外的变量。这工作正常：

  With ThisWorkbook 
 
 .Sheets（cstrParamTab）.ChartObjects（IntradayChart1） .Chart.SetSourceData _ 
 Source：=。Sheets（cstrChartBaseTab）.Range（cstrColTimeStamp& clngTopRow&：& cstrColValueClose& plngLastRow），PlotBy：= xlColumns 
 
 End使用
  

定义选项卡和范围的变量是预定义的常量和变量。你可以这样做：

  With ThisWorkbook 
 
 .Sheets（Parameters）ChartObjects （IntradayChart1）Chart.SetSourceData _ 
来源：=。Sheets（ChartBase）。范围（B2：B239），PlotBy：= xlColumns 
 
结束
  

但如果您的图表是动态的，则不会起作用。

祝你好运

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.

解决方案

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

这篇关于Excel 2010 VBA ActiveChart.SetSourceData失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！