在 Excel、VBA (Excel 2010) 中设置 PlotArea.Width 时出错

Error setting PlotArea.Width in Excel, VBA (Excel 2010)

提问by David Zemens

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

我在尝试设置图表的 plotarea.width 属性的子程序中遇到错误。

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"

如果我注释掉前面的行，其他维度也会导致此错误。没有ActiveChart，没有选择等。具体的错误信息是这样的：“-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.

• 在调试模式下，按 F8 单步执行代码不会发生错误。
• AFAIK“宽度”不是“方法”而是图表绘图区域的“属性”，因此即使是错误消息也相当不明确。

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.

有什么想法吗？这是我可以分享的尽可能多的代码，完整的 ChartSizeMedium 子例程，以及一个虚拟片段，向您展示我如何建立图表并将其传递给设置大小和其他一些属性的子程序，然后再传递给另一个函数将系列数据添加到图表中。

    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

2012 年 12 月 12 日更新

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:

我删除了所有没有问题的代码，只使用了带有块的 PlotArea，在同一个例程中，我还尝试设置图表类型（几个值），如本例所示，在尝试设置之前手动添加一系列数据PlotArea 尺寸，但错误仍然存​​在：

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

采纳答案by David Zemens

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 selectingthe 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.

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

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 knowthat within that loop it is failing once on each of the properties before it successfully sets them on a subsequent pass.

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

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

回答by Patrick Lepelletier

i had a similar problem . And its definitely an excel issue (having 2013).

我有一个类似的问题。这绝对是一个 excel 问题（有 2013 年）。

With .PlotArea 
    .Select 'err if delete this line of code
    .Top = 0
    .Left = 0
    .width = 40
    .Height = 40 
End With

if you remove the .selectline, it will result in error on the next line. note that i am not working with a <with selectiondo stuff>. the .selectmakes it work, without using the selection, wich is obviously an excel bug (from previous versions?)

如果删除该.select行，则会导致下一行出错。请注意，我不是在使用 < with selectiondo stuff>。将.select使得它的工作，而无需使用的选择，至极显然是一个Excel漏洞（以前版本？）

回答by user2140261

I know this is old, and this solution seems bad, but it works. I only thought to do it as you mention that stepping through works.

我知道这是旧的，这个解决方案看起来很糟糕，但它有效。当你提到逐步完成工作时，我才想到这样做。

Option Explicit

Sub chart()

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)
cObj.chart.ChartType = 57
Set srs = cObj.chart.SeriesCollection.NewSeries
    srs.Values = "={1,3,5,7,4}"
Application.Wait Now + TimeValue("00:00:01") '<~~ Added This line
With cObj.chart.PlotArea
    .Width = 100
    .Height = 280
    .Left = 30
    .Top = 30
End With
End Sub

回答by David Zemens

EDIT:This seems to work for somechart types, but it was still failing for other chart types. I have continued to use the 5x loop with On Error Resume Nextand that seems to be -- unfortunately -- the most "reliable" solution to-date.

编辑：这似乎适用于某些图表类型，但对于其他图表类型仍然失败。我继续使用 5x 循环，On Error Resume Next这似乎是 - 不幸的是 - 迄今为止最“可靠”的解决方案。

Original: This is based on User2140261's suggested answer, above:

原文：这是基于上面 User2140261 的建议答案：

https://stackoverflow.com/a/16041640/1467082

https://stackoverflow.com/a/16041640/1467082

Since the question was initially posted, the application now resides in PowerPoint, so I cannot use the Applicaiton.Wait. I had some intermittent errors with a 1-second pause, and 3-seconds was too much of a pause, so I built the following error trap. The same idea could be used in Excel in conjunction with Application.Wait.

由于问题最初是发布的，该应用程序现在驻留在 PowerPoint 中，因此我无法使用Applicaiton.Wait. 我有一些间歇性错误，暂停 1 秒，而 3 秒暂停太多了，所以我构建了以下错误陷阱。同样的想法可以在 Excel 中与Application.Wait.

It was this block of code that was giving me fits, so I added this error handling in Powerpoint to mimic the Application.Wait.

正是这段代码让我适应了，所以我在 Powerpoint 中添加了这个错误处理来模仿Application.Wait.

RetryChartDimensions:
On Error GoTo ErrChartDimensions
With .PlotArea
    .Width = paWidth
    .Height = paHeight
    .Left = paLeft
    .Top = paTop
End With
On Error GoTo 0

' More code
' more code

Exit Sub 'gracefully exit this subroutine before the error-handling.'

ErrChartDimensions:
Err.Clear
'Pause before setting the PlotArea dimensions:'
Dim wtTime As Double
Dim startTime As Long

'A maximum 3 second delay should be more than enough time.
If wtTime < 3 Then
    wtTime = wtTime + 0.5
    startTime = Timer
    While Timer < startTime + wtTime
        DoEvents
    Wend
End If
Resume RetryChartDimensions

End Sub

回答by bonCodigo

Hope your sheet and chart has the ability to get a width up to 640. If so try the explicitreference. Also suggest you to change width, heightvalues to lower values and see how programme responds. Since you said, when you selectit works,

希望您的工作表和图表能够将宽度增加到640. 如果是这样，请尝试显式引用。还建议您将width, height值更改为较低的值并查看程序如何响应。既然你说，当你select工作的时候，

• that also means, your chtis wrapping the correct chart object - unless otherwise you selected the chart using ActiveChart.PlotArea.Width. Hence I guess explicit reference could do be a potential try out.

• 这也意味着，您cht正在包装正确的图表对象 - 除非您使用ActiveChart.PlotArea.Width. 因此，我想明确的参考可能是一种潜在的尝试。

,

,

cht.PlotArea.Width = 640
cht.PlotArea.Height = 280
cht.PlotArea.Left = 30
cht.PlotArea.Top = 30

Further, check on Aspect Ratiolock or unlock. If none of these works, then add a chart into your sheet and use most simple chart formatting code to check on widht, height, left, topchanges.

此外，检查Aspect Ratio锁定或解锁。如果这些都不奏效，则将图表添加到您的工作表中，并使用最简单的图表格式代码来检查widht, height, left, top更改。

---

---

Update two

更新二

Let's try specifing chart type and setting up chart object in the second subas well. I tried out in my end and it's working. Try the code with following changes.

让我们尝试在第二个中sub指定图表类型并设置图表对象。我最后尝试了，它正在工作。尝试使用以下更改的代码。

Code: Calling this sub from the button showed in the sheet.

代码：从工作表中显示的按钮调用此子程序。

Option Explicit

  Public Sub EstablishChartObject()
  Dim mySheet As Worksheet
  Dim cObj As ChartObject

    Application.ScreenUpdating = False
    Application.StatusBar = "Chart is coming soon..."

    Set mySheet = Sheets(2) '-- set according to yours
    '-- create chart with some source data first, which you can change later
    Set cObj = mySheet.ChartObjects.Add(Left:=30, Top:=30, Width:=400, Height:=200)
    ChartSizeMedium cObj, "Integer", "Example Chart Title"

  End Sub


  'Subroutine to make a consistent size chart
  Private Sub ChartSizeMedium(chtObj As ChartObject, NumType As String, _
                                  Optional chtTitle As String)

  Dim myChart As Chart
  Dim s As Long

     Set myChart = chtObj.Chart '-- specify chart type
     myChart.SetSourceData Source:=Sheets(2).Range("B3:C12")  '-- set to what you have
     myChart.ChartType = xlXYScatterLines  '-- set to the type you want 
                                          'and make sure to **use correct properties**

        With myChart
            If .HasTitle Then
                .ChartTitle.Characters.Text = chtTitle
            End If

            'Create the default chart Legend
            If .HasLegend Then
                With .Legend
                    .Position = xlTop
                    .Font.Size = 11
                    .Font.Bold = True
                End With
            End If

            'Format the axes
            With .Axes(xlValue)
                .HasMajorGridlines = False
            End With

            'Format the size of the chart
            With .Parent
                .Width = 400 '-- change to yours
                .Height = 250 '-- change to yours
            End With

            With .PlotArea
                .Width = 300 '-- change to yours
                .Height = 180 '-- change to yours
                .Left = 30
                .Top = 30
            End With
    End With

    Application.ScreenUpdating = True
    Application.StatusBar = "Chart is Here!"

End Sub

Otput:

输出：

Make sure to use correct properties for each chart type.Note that above code doesn't delete any left over, old charts from your sheet.

确保为每种图表类型使用正确的属性。请注意，上面的代码不会从您的工作表中删除任何剩余的旧图表。

.MajoreGridlines.Format.Lines.Visiblefails. So set the .MajorGridlines = Falseto make sure you do not want to show the gridlines. Anything else you want to do can be done later. Just try with the changes to dimension initially.

.MajoreGridlines.Format.Lines.Visible失败。所以设置.MajorGridlines = False以确保您不想显示网格线。您想做的任何其他事情都可以稍后完成。刚开始尝试更改维度。

Reference from : MSDN Gridlines property

参考：MSDN Gridlines 属性

回答by Kristian

I don't have enough reputation to add a comment, so using the above solutions I have fixed my problem with Pie Charts in VB.Net 2010 and Excel 2013. xlLine charts never caused a problem, but my code would crash when the same code was ran against an xlPie chart on Excel 2013 (All was fine on Excel 2007).

我没有足够的声誉来添加评论，因此使用上述解决方案我已经解决了我在 VB.Net 2010 和 Excel 2013 中使用饼图的问题。xlLine 图表从未引起问题，但是当相同的代码时我的代码会崩溃在 Excel 2013 上针对 xlPie 图运行（在 Excel 2007 上一切正常）。

My now working code:

我现在的工作代码：

    appExcel.Visible = False
    xlchart_for_96_Well_Plate_Source = appExcel.Charts.Add(After:=wkbExperiment_Details.Sheets(wkbExperiment_Details.Sheets.Count))
    appExcel.ScreenUpdating = False

    With xlchart_for_96_Well_Plate_Source
            .SetSourceData(Source:=wksData.Range(wksData.Cells(2, byteCharts_added), wksData.Cells(intUsed_Rows, byteCharts_added)), PlotBy:=Microsoft.Office.Interop.Excel.XlRowCol.xlColumns)
            .ChartType = objChart_Type
            .PlotArea.Select() 
            .PlotArea.Top = 2
            .PlotArea.Select()
            .PlotArea.Left = 2
            .SeriesCollection(.SeriesCollection.count).xvalues = wksData.Range(wksData.Cells(2, 1), wksData.Cells(intUsed_Rows, 1)).Value ' Scale - wavelength for line chart
            .SeriesCollection(.SeriesCollection.count).Values = wksData.Range(wksData.Cells(2, byteCharts_added + 1), wksData.Cells(intUsed_Rows, byteCharts_added + 1)).Value
            .SeriesCollection(.SeriesCollection.count).Name = wksData.Cells(1, .SeriesCollection.count + 1).value
    End With
appExcel.ScreenUpdating = True