Use VBA to Add a Chart

There are four (well, five) VBA methods you can use to add a chart to your Excel workbook. Some are old and obsolete, but I’ll cover them all in case you encounter them in old code. I’ll discuss them starting with the oldest.

Note: I only use the latest method in my commercial Excel charting add-ins, since Microsoft has already ended support for versions that don’t use it.

Charts.Add

This is the oldest VBA syntax to add a chart. In the old days, there were only separate charts, which evolved into chart sheets. A chart sheet is a standalone chart on a sheet with no rows, columns, or cells. I don’t think they’re used much anymore, since it’s common practice to put several charts together on a worksheet, often alongside the data being plotted.

The syntax looks like the following. I’ve included an additional line of code that moves the chart from its own chart sheet onto the worksheet.

Charts.Add([Before], [After], [Count])
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

The optional arguments allow you to specify which other sheet to add the chart sheet before or after, and how many charts to insert. If nothing is specified, one chart is inserted before the active sheet.

The inserted chart type uses the default chart type, which is a clustered column chart unless you’ve defined a different default chart type.

Don’t use this syntax; it’s obsolete. If you want to insert a chart sheet for any reason, use Charts.Add2 described near the end of this article.

ChartObjects.Add

A ChartObject is the shape that encloses an embedded chart on a worksheet. This method inserts such a ChartObject on the specified worksheet. All of the arguments are required.

ActiveSheet.ChartObjects.Add(Left, Top, Width, Height)

This method is obsolete and should not be used in new code. The chart type is the default chart type (clustered column chart). The style is the first style for the default chart type in the Excel 2007/2010 chart style gallery, and looks dated.

Shapes.AddChart

Added in Excel 2007, this method is an improvement over ChartObjects.Add because all arguments are optional. You can also specify the chart type; if omitted, a default chart is inserted. If you don’t specify its size, the default 5-inch wide x 3-inch high chart is produced. If you don’t specify a position, the new chart is centered within the active window.

Shapes.AddChart([XlChartType], [Left], [Top], [Width], [Height])

Adding a shape that contains a chart is no different than adding a ChartObject that contains a chart. While an improvement over ChartObjects.Add, the chart produced by Shapes.AddChart uses the first style for its chart type in the Excel 2007/2010 chart style gallery, and looks dated.

Shapes.AddChart2

Tl;dr. Shapes.AddChart2 is the VBA method you should be using to add charts to your worksheet.

This method introduced in Excel 2013 improves on Shapes.AddChart with two additional optional arguments, Style and NewLayout. You can specify the chart type; if omitted, the default chart type is used. You can specify the chart style; if omitted, the default style of the chart type created is applied. Since this style is in the Excel 2013+ chart style gallery, the chart will look more up-to-date.

If you don’t specify its size, the default 5-inch wide x 3-inch high chart is produced. If you don’t specify a position, the chart is centered within the active window.

Shapes.AddChart2([Style], [XlChartType], _
    [Left], [Top], [Width], [Height], [NewLayout]) 

If NewLayout is True, the chart will use new layout features, meaning you always get a chart title, and you only get a legend if there are two or more series in the initial chart.

Charts.Add2

This method was also included in Excel 2013 and looks much like Charts.Add, with the addition of the NewLayout argument. It inserts a chart sheet, that you would have to move to the appropriate worksheet. The inserted chart has the default type (e.g., clustered column), but at least the style comes from the better-looking Excel 2013+ style gallery.

Add2([Before], [After], [Count], [NewLayout])
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

If NewLayout is True, the chart will use new layout features, meaning you always get a chart title, and you only get a legend if there are two or more series in the initial chart.

If I wanted a chart sheet, I would not use Charts.Add2; I would create an embedded chart using my preferred style and type using Shapes.AddChart2, and then move the chart to a new chart sheet.

ActiveSheet.Shapes.AddChart([Style],[XlChartType]).Location _
    Where:=xlLocationAsNewSheet

Putting it Together

The code below is a well-written VBA routine to insert a chart. It starts by declaring a chart variable, then assigning it to the inserted chart, and finally modifies the properties of that chart.

Dim cht As Chart
Set cht = Worksheets("My Charts").Shapes.AddChart _
    (332, xlLineMarkers, 100, 100, 360, 216).Chart
With cht
  ' adjust data
  ' modify chart formats and properties
End With

Note: the default style for a line-with-markers chart is 332. If you need to use one of the other styles in the gallery (please don’t) you can find the style number by recording a macro while you apply that style to a chart.