Excel 2010 vba AddChart.Chart vs ChartObjects.Add ChartWizard

Topic

New Reply

I was trying out creating a chart with vba using chartwizard. I cut and pasted some formatting code that I’ve used previously, but I get the following error:

“Object doesn’t support this property or method”

at .ChartArea.Select.

I’ve tried several ways to reference the chart object and I seem to be missing something in the concept and implementation for the creation with chartwizard.

The code that fails follows and I’ve attached a worksheet with both sets of code and data. The sub lenfreq() runs and cust() gets the error.

Code:

Sub cust()
Dim charttemp As ChartObject
Dim ch As ChartObject
Dim chartname As Variant
Dim chartindex As Variant
Set charttemp = Worksheets(“sheet1”).ChartObjects.Add(175, 30, 600, 375)
charttemp.Chart.ChartWizard Source:=Worksheets(“sheet1”).Range(“g2:i41″), _
    gallery:=xlColumnClustered, HasLegend:=False, _
    Title:=”Length Frequency Distribution of all Sizes by Month” & Chr(10) & Cells(2, 2) & Chr(10) & “(” & Cells(2, 3) & “)”, _
    CategoryTitle:=”Range of Lengths (mm) by Month”, _
    Valuetitle:=”Frequency of Lengths”
chartname = charttemp.Name

With charttemp
        ‘ get object error here (Object doesn’t support this property or method)
        .ChartArea.Select         
        ‘title1len = Cells(1, 6).Characters.Count
        With charttemp.ChartTitle
            .Font.Size = 12
            .Characters(1, 30).Font.Size = 18   ‘title1len
            
        End With
        With .Axes(xlValue)
            .HasMajorGridlines = False
            .HasTitle = True
            With .AxisTitle
                ‘.Text = Cells(1, 3)
                .Font.Name = “calibri”
                .Font.Size = 12
                .Font.Bold = True
            End With
                
        End With
        With .Axes(xlCategory)
            .HasMajorGridlines = False
            .HasTitle = True
            With .AxisTitle
                ‘.Text = Cells(1, 2)
                .Font.Name = “calibri”
                .Font.Size = 12
                .Font.Bold = True
            End With

        End With
End With

End Sub

Reply | Quote

Replies

Required an activate, which I had already tried and the one thing I did not try before posting, working with the ActiveChart. Another error in the code above is the missing .HasTitle. Code snip follows.

Code:

charttemp.Activate
With ActiveChart
       ‘title1len = Cells(1, 6).Characters.Count
        .HasTitle = True
        With ActiveChart.ChartTitle
…………………………………

So, why doesn’t do something with this object variable work in this case? Ie why does “with charttemp” work when using Chartobjects.Add and does not work when a chart is added using the ChartWizard?

Reply | Quote

Charttemp is a ChartObject, which contains a Chart. It is not itself a chart. So you need something like:

Code:

With charttemp.Chart
        ' get object error here (Object doesn't support this property or method)
        .ChartArea.Select

Reply | Quote

In Excel 2010 charttemp object is NOT a ChartObject but a shape object containing a Chart

In Excel 2003 ChartObjects.Add returns a ChartObject
In Excel 2010 ChartObjects.Add returns a Shape

dim shp as Shape
dim chtOBJ as chartobject
set shp = Activeworksheet.Chartobjects.Add

set chtobj = shp.chart.parent ‘ if require to reference chartobject

Reply | Quote

No it doesn’t – it still returns a ChartObject. Shapes.Addchart would return a shape though.

Reply | Quote

You are right Rory,

Different object type is returned by Chartobject.Duplicate , not chartobjects.add where same type is returned on Excel 2003 and 2010

Reply | Quote