How should I create graphs in Excel using VBA?

Question

I am making a report on Excel that will create graphs for the user based on data entered into the spreadsheet. I am quite new to VBA and, whilst I think I'm getting the hang of some basic expressions and structures, I'm definitely having trouble getting my head around the options available for coding graphs.

---

Basic Google searches produce lots of guides, the following being examples:

https://peltiertech.com/Excel/ChartsHowTo/QuickChartVBA.html

https://www.thespreadsheetguru.com/blog/2015/3/1/the-vba-coding-guide-for-excel-charts-graph

...and searches on Stack Overflow and looking through the Microsoft help pages provide a certain amount of insight. However, there seem to be several ways of making graphs using VBA, and I'm struggling to understand the nuances in different examples of code.

---

For example, I'm hesitant about which variables I should use for the chart. What might be the consequences in using Dim cht As Chart as opposed to As Shape or As ChartObject? I imagine this might inform the expressions I'd consider using to create the actual graph - which, according to the examples, seem to include:

Set cht = ws.Shapes.AddChart2(201, xlColumnClustered)

Or:

ws.ChartObjects.Add(*Left, Top, Width, Height*)

Or:

Set chtSht = Charts.Add

...Each of which seem quite different and I ultimately don't understand what purposes the differences serve.

I have a hunch it's mostly to do with whether the charts are embedded or not, though I'm not sure of the extent to which this would inform the code.

So, for those who are more experienced than I am and can probably address these concerns more efficiently than I can digest Google search results: How should I go about creating graphs in Excel using VBA?

Answer 1

There are four syntaxes to add an embedded chart to a worksheet. I'll list them oldest first.

The oldest way, adds a chart sheet, then places the chart sheet onto the worksheet. Don't use it, it's obsolete.

Charts.Add
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

This way adds the ChartObject directly to the worksheet. A ChartObject is a special kind of shape that includes a chart. This adds a default chart (clustered column unless you've defined a different default), using the dimensions and positions in parentheses (which are required). It returns a ChartObject.

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

Excel 2007 introduced this syntax, which includes the chart type (if omitted you get the default type) and position and size (if omitted the chart is the default size, 3 by 5 inches, in the center of the active window). It returns a Shape.

ActiveSheet.Shapes.AddChart(XlChartType, Left, Top, Width, Height)

Excel 2013 introduced another syntax, which also includes the chart style in addition to the other parameters, and which also returns a shape.

ActiveSheet.Shapes.AddChart2(Style, XlChartType, Left, Top, Width, Height)

I would like to use AddChart2 all the time, but I still support users of Excel 2010, so I have to use AddChart. I have a routine that looks up the default style for a given chart type, so I can apply it in Excel 2013+ to mimic AddChart2. There is probably no reason to use ChartObjects.Add over AddChart or AddChart2; no need to rewrite code if it's working, but don't use it in new code.

What you should do is declare a variable of type Chart, and set it as follows:

Dim cht As Chart

Set cht = ActiveSheet.ChartObjects.Add(100, 100, 360, 216).Chart

Set cht = ActiveSheet.Shapes.AddChart(xlXYScatterLines, 100, 100, 360, 216).Chart

Set cht = ActiveSheet.Shapes.AddChart2(240, xlXYScatterLines, 100, 100, 360, 216).Chart

Now you can perform further operations on the chart assigned to cht.