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

excel, excel-2010, vba

Solution

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

Problem

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

Original source