Changing a chart object with Excel VBA

Question

I am working on a larger project that involves making a worksheet with up to 100 different charts (in this case XY scatter charts). I have seen on other threads that it is not efficient to activate charts every time you want to make a change. In this sub I am just running through a loop to create 120 blank charts, and will later add data and format. Here is my code:

Global Const numCharts = 120
Private Charts()
ReDim Charts(numCharts)

Sub createCharts()
    For graphIndex = 1 To numCharts
        'adds the new chart in the specified loaction
        Set Charts(graphIndex) = ActiveSheet.ChartObjects.Add(Left:=chartTopLeftX, Top:=chartTopLeftY, Width:=chartWidth, Height:=chartHeight)
        Charts(graphIndex).Name = activeSheetName & graphIndex
        Charts(graphIndex).ChartType = xlXYScatter

        'changes the top left coordinate varibales for the following chart
        If graphIndex Mod 2 = 1 Then
            chartTopLeftX = chartTopLeftX + chartWidth + chartGap
            'chartTopLeftY = chartTopLeftY
        Else
            chartTopLeftX = chartTopLeftX - chartWidth - chartGap
            chartTopLeftY = chartTopLeftY + chartHeight + chartGap
        End If
    Next graphIndex
End Sub

The Charts(graphIndex).Name line works fine, but I get an error on the Charts(graphIndex).ChartType line. If I activate the chart first I can just use ActiveChart.ChartType, but I am trying to avoid that. Hopefully this makes sense. I have run into this issue in the past when working with chart objects, but can't seem to find a good explanation on why?

Edit: I'm sorry I should have specified this, but there are many variables decalred in the module, outside of this sub. The code runs perfectly fine without the Charts(graphIndex).ChartType line. I may not be handling this correctly, but I tried to create an array to hold the chart objects.

this Charts(graphIndex) will error out as #9. the code you have posted will never get past that line. — cyboashu
– cyboashu, Commented Feb 1, 2018 at 19:05
@cyboashu Sorry still new to posting on here. I edited now, but the Charts() array was declared and defined separately in the module. The issue I was having was just with changing the chart type without having to activate the chart. — Chris Hill
– Chris Hill, Commented Feb 1, 2018 at 19:30

David Zemens · Accepted Answer · 2018-02-01 19:10:13Z

0

Observe that ChartType is a member of the Chart object, not the ChartObject object. So to assign the ChartType, you need to handle the ChartObject.Chart.

Sub createCharts()
    Dim newChart as ChartObject
    For graphIndex = 1 To numCharts
        'adds the new ChartObject in the specified loaction
        Set newChart = ActiveSheet.ChartObjects.Add(Left:=chartTopLeftX, Top:=chartTopLeftY, Width:=chartWidth, Height:=chartHeight)
        newChart.Name = activeSheetName & graphIndex
        'handles the .Chart:
        newChart.Chart.ChartType = xlXYScatter

NOTE: This will potentially fail with an Index Out of Range error, unless the chart already exists. So your code either doesn't work at all (you can't add a chart this way, to a sheet with 0 charts on it), or it's replicating a bunch of extra charts, unnecessarily. You'll need to fix that.

Alternatively, chain the Chart property of the ChartObjects.Add, like:

Set Charts(graphIndex) = ActiveSheet.ChartObjects.Add(Left:=chartTopLeftX, Top:=chartTopLeftY, Width:=chartWidth, Height:=chartHeight).Chart

But rather than referring to them constantly as indexed members of the Charts collection, I think the first approach to use an object variable is best. If you don't like to work with the ChartObject, just combine the two methods above:

Dim cht as Chart
Set cht = ActiveSheet.ChartObjects.Add( _
    Left:=chartTopLeftX, _
    Top:=chartTopLeftY, _
    Width:=chartWidth, _
    Height:=chartHeight).Chart
cht.Parent.Name = activeSheetName & graphIndex
cht.ChartType = xlXYScatter

answered Feb 1, 2018 at 19:10

David Zemens

53.8k12 gold badges86 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

Chris Hill Over a year ago

Thank you, that worked. Is there an easy way to keep track of which properties are linked to the Chart property and which are linked to the ChartObject?

David Zemens Over a year ago

@ChrisHill well the Intellisense should tip you off if you're using strongly-typed variables, but implicit collections don't always play nicely. You should bookmark the Excel object model reference, here and refer to the pages on each Chart and ChartObject respectively. (There's a wealth of other information there, as well!)

Chris Hill Over a year ago

To address the duplicate charts, this is just one sub in a series of about 10 that regenerates the charts. The first sub always deletes all current charts. Then this sub is called to recreate them.

David Zemens Over a year ago

sure, that's out of scope, I just mention it as a possible problem since I don't have your full code. In any case. Set Charts(graphIndex) = ActiveSheet.ChartObjects.Add(... seems like it will always fail if the Charts collection doesn't already contain graphIndex number of charts.

Chris Hill Over a year ago

I am not having that issue, but I think it was due to luck and not my understanding (not generally a good thing when coding). I edited my original post to show the declaration and defining of Charts(). Is there a different way I should handle this? The reason I chose to use an array, was it seems easier to reference and edit in other subs. Later I add data, format, and edit axis to each chart using other subs. This seemed like a decent way to handle this.

|

Jon Peltier · Accepted Answer · 2018-02-11 21:07:38Z

Building on the answer of @David-Zemens:

ChartObjects.Add is older syntax, which still works fine, but newer syntax might serve you better in this case.

Use this to build the charts (2007+):

Dim cht as Chart
Set cht = ActiveSheet.Shapes.AddChart( _
    XlChartType:= xlXYScatter, _
    Left:=chartTopLeftX, _
    Top:=chartTopLeftY, _
    Width:=chartWidth, _
    Height:=chartHeight).Chart
cht.Parent.Name = activeSheetName & graphIndex

or this (2013+):

Dim cht as Chart
Set cht = ActiveSheet.Shapes.AddChart2( _
    Style:=240, _
    XlChartType:= xlXYScatter, _
    Left:=chartTopLeftX, _
    Top:=chartTopLeftY, _
    Width:=chartWidth, _
    Height:=chartHeight, _
    NewLayout:=True).Chart
cht.Parent.Name = activeSheetName & graphIndex

AddChart inserts a chart with the Office 2007 default styling, AddChart2 inserts one with the Office 2013 styling, with the option of applying a different style. Style 240 is the default style for an XY Scatter chart in 2013, other chart types have their own styles.

FWIW, I always add a chart and populate and format it, then add the next chart. Otherwise it's too complicated and too much bookkeeping, and I'm always afraid too much can go wrong.

Collectives™ on Stack Overflow

Changing a chart object with Excel VBA

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related