错误设置Excel中的PlotArea.Width，VBA（Excel 2010） [英] Error setting PlotArea.Width in Excel, VBA (Excel 2010)

问题描述

其他维度也会导致此错误，如果我注释掉前面的一行。
没有ActiveChart，没有选择等。具体的错误信息是这样的：-2147467259（80004005）对象PlotArea失败的方法'Width'

这是因为以下几个原因：

• 在调试模式下，F8逐步通过代码不会发生错误。


• AFAIKwidth不是图表的plotarea的方法，而是属性，所以即使是错误信息也是模糊的。






任何想法？下面是可以分享的ChartSizeMedium子例程的完整代码，以及一个虚拟代码段，用于显示我如何建立图表，并将其传递给设置尺寸&一些其他属性，然后传递给另一个功能，将系列数据添加到图表。

  Option Explicit 
 Private Sub SetChartObject（）
 Dim cObj作为ChartObject 
设置cObj = ActiveSheet.ChartObjects.Add（左：= 30，顶部：30，宽度：= 740，高度：= 300）
 ChartSizeMedium cObj。图表整数，示例图表标题
 End Sub 
 Private Sub ChartSizeMedium（cht As Chart，NumType As String，可选chtTitle As String）
'制作一致的大小图表的子程序
 Dim s As Long 
带有cht 
'如果存在图表标题，则添加。 
如果Len（chtTitle）> 0然后
 .HasTitle = True 
 .chartTitle.Characters.Text = chtTitle 
 End If 
'创建默认图表Legend 
 .HasLegend = True 
使用.Legend 
 .Position = xlTop 
 .Font.Size = 11 
 .Font.Bold = True 
结束
'格式化轴
。 Axes（xlValue）.MajorGridlines.Format.Line.Visible = msoFalse 
 .Axes（xlValue）.MinorGridlines.Format.Line.Visible = msoFalse 
 
'格式化图表的大小
与.Parent 
 .Width = 740 
 .Height = 396 
结束
 
带.PlotArea 
 .Width = 640'<  - ---此线路触发错误
 .Height = 280 
 .Left = 30 
 .Top = 30 
结束
结束
'一些图表从多个系列容器开始，所以确保它们已经消失了：
与cht 
直到.Se riesCollection.Count = 0 
s = .SeriesCollection.Count 
 .SeriesCollection（s）.Delete 
循环
结束
 End Sub 
  

更新2012年12月12日





我删除所有无问题的代码，并且仅使用具有块的PlotArea，在同一例程中，我也尝试设置图表类型（几个值），如本示例所示，在尝试设置PlotArea之前手动添加一系列数据维度，但是错误仍然存​​在：

  Option Explicit 
 Private Sub建立对象2（）
 Dim cObj As ChartObject 
 Dim sh As Worksheet 
 
设置sh = ActiveSheet 
 Dim srs As Series 
设置cObj = sh.ChartObjects.Add（左：= 30，顶部：= 30 ，宽度：= 740，高度：= 300）
设置srs = cObj.Chart.SeriesCollection.NewSeries 
 
 srs.Values == {1,3,5,7,4} 
 cObj.Chart.ChartType = 57 
 
带cObj.Chart.PlotArea 
 .Width = 100'<----该线路触发错误
 .Height = 280 
 .Left = 30 
 .Top = 30 
结束
 
 End Sub 
  




解决方案

要工作，既不喜欢我喜欢的优雅（我希望有一种办法可以用选择图表或其任何部分）。





选项1 - 选择绘图区域，然后取消选择。这似乎是最可靠/最有效的解决方案。

 使用.PlotArea 
 Application.ScreenUpdating = False 
。选择
带选择
 .Width = paWidth 
 .Height = paHeight 
 .Left = paLeft 
 .Top = paTop 
 ActiveSheet.Range（A1）。激活
结束
 Application.ScreenUpdating = True 
结束
  

选项2 - 禁用循环中的错误处理（从Doug的链接出发）。这似乎不是一个非常可靠或有效的方法，虽然它似乎工作，但我知道在该循环中，它在每个属性上失败一次，然后成功将它们设置为

 使用.PlotArea 
对于pLoop = 1至5 
错误恢复下一步
 .Width = paWidth 
 .Height = paHeight 
 .Left = paLeft 
 .Top = paTop 
错误GoTo 0 
下一个
结束与
  




I am experiencing an error in a subroutine attempting to set the plotarea.width property of a chart.

The other dimensions also cause this error if I comment out the preceding line(s). There is no ActiveChart, no selection, etc. The specific error message is this: "-2147467259 (80004005) Method 'Width' of object 'PlotArea' failed"

This is stumping me for several reasons:

• In debug mode, F8 to step through the code the error does NOT occur.
• AFAIK "width" is not a "method" but a "property" of the chart's plotarea, so even the error message is rather ambiguous.

Any thoughts? Here's as much code as I can share, the ChartSizeMedium subroutine in its entirety, and a dummy snippet to show you how I am establishing the chart and passing it to that sub which sets the size & some other properties prior to passing to another function which adds the series data to the chart.

    Option Explicit
    Private Sub EstablishChartObject()
    Dim cObj as ChartObject
    Set cObj = ActiveSheet.ChartObjects.Add(Left:=30, Top:30, Width:=740, Height:=300)
        ChartSizeMedium cObj.Chart, "Integer", "Example Chart Title"
    End Sub
    Private Sub ChartSizeMedium(cht As Chart, NumType As String, Optional chtTitle As String)
    'Subroutine to make a consistent size chart
    Dim s As Long
    With cht
    'Add a chart title if one exists.
        If Len(chtTitle) > 0 Then
        .HasTitle = True
        .chartTitle.Characters.Text = chtTitle
        End If
    'Create the default chart Legend
        .HasLegend = True
        With .Legend
        .Position = xlTop
        .Font.Size = 11
        .Font.Bold = True
        End With
    'Format the axes
        .Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
        .Axes(xlValue).MinorGridlines.Format.Line.Visible = msoFalse

    'Format the size of the chart
        With .Parent
        .Width = 740
        .Height = 396
        End With

        With .PlotArea
        .Width = 640    '<---- THIS LINE TRIGGERS THE ERROR
        .Height = 280
        .Left = 30
        .Top = 30
        End With
    End With
    'Some charts start with more than one series container, so make sure they're gone:
    With cht
    Do Until .SeriesCollection.Count = 0
    s = .SeriesCollection.Count
    .SeriesCollection(s).Delete
    Loop
    End With
    End Sub

UPDATE Dec 12, 2012

I remove all non-problematic code and use only the PlotArea with block, in the same routine, I have also tried setting the chart type (several values) and as shown in this example, manually adding one series of data prior to attempting to set the PlotArea dimensions, but the error persists:

Option Explicit
Private Sub EstablishChartObject2()
    Dim cObj As ChartObject
    Dim sh As Worksheet

    Set sh = ActiveSheet
    Dim srs As Series
    Set cObj = sh.ChartObjects.Add(Left:=30, Top:=30, Width:=740, Height:=300)
    Set srs = cObj.Chart.SeriesCollection.NewSeries

    srs.Values = "={1,3,5,7,4}"
    cObj.Chart.ChartType = 57

    With cObj.Chart.PlotArea
        .Width = 100   '<---- THIS LINE TRIGGERS THE ERROR
        .Height = 280
        .Left = 30
        .Top = 30
    End With

End Sub

解决方案

Two solutions that seem to be working, neither is really as "elegant" as I'd prefer (I was hoping there would be a way to do this with selecting the chart or any part of it).

Option 1 - Select the plot area and then deselect it. This seems to be the most reliable/efficient solution.

With .PlotArea
    Application.ScreenUpdating = False
   .Select
    With Selection
        .Width = paWidth
        .Height = paHeight
        .Left = paLeft
        .Top = paTop
        ActiveSheet.Range("A1").Activate
    End With
    Application.ScreenUpdating = True
End With

Option 2 - disable error-handling in loop (this followed from Doug's link). This doesn't seem to be a very reliable or efficient method, and although it seems to work, I know that within that loop it is failing once on each of the properties before it successfully sets them on a subsequent pass.

With .PlotArea
    For pLoop = 1 To 5
        On Error Resume Next
        .Width = paWidth
        .Height = paHeight
        .Left = paLeft
        .Top = paTop
        On Error GoTo 0
    Next
End With

这篇关于错误设置Excel中的PlotArea.Width，VBA（Excel 2010）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！