Guide to Creating Charts with a Macro in Excel

How to add, edit, and position charts in Excel using VBA.

This tutorial covers what to do when adding the chart.

Sections:

Add a Chart with VBA/Macros

Set Chart Source Data

Set Chart Type

Change Chart Title

Set Category and Value Titles

Include a Legend

Change Chart Size and Position

Fewer Arguments

Notes

Add a Chart with VBA/Macros

Sub CreateChart()

'Tell the macro to make a variable that can hold the chart.
Dim myChart As ChartObject

'Create a chart and put the newly created chart inside of the variable created above.
Set myChart = Worksheets("sheet1").ChartObjects.Add(10, 80, 300, 175)

'Add some basic elements to the chart
myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

End Sub

This is the basic code for adding a chart to a worksheet using a macro. Each section of code in the macro has a comment that explains what that section does, also, each section below here explains how to change and use the macro in more detail.

Set Chart Source Data

This tells the chart what data to use.

Sub CreateChart()

'Tell the macro to make a variable that can hold the chart.
Dim myChart As ChartObject

'Create a chart and put the newly created chart inside of the variable created above.
Set myChart = Worksheets("sheet1").ChartObjects.Add(10, 80, 300, 175)

'Add some basic elements to the chart
myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

End Sub

Source:=Worksheets("Sheet1").Range("A1:E4") this holds the range reference for the chart.

Worksheets("Sheet1").Range("A1:E4") is the range reference.

Make sure to specify from which worksheet you want to get the source data, otherwise, it will come from the currently active or visible worksheet.

Possible Values

Any range from any worksheet, including named ranges.

Set Chart Type

This controls the type of chart to use.

Sub CreateChart()

'Tell the macro to make a variable that can hold the chart.
Dim myChart As ChartObject

'Create a chart and put the newly created chart inside of the variable created above.
Set myChart = Worksheets("sheet1").ChartObjects.Add(10, 80, 300, 175)

'Add some basic elements to the chart
myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

End Sub

Gallery:=xlLine the name of this argument is Gallery, which can seem confusing, but it just controls the type of chart that will be used to display your source data. xlLine is the type of chart that will be used - you can view a full list of possible chart types below here.

List of Possible Chart Types:

Change Chart Title

Title of the chart.

Sub CreateChart()

'Tell the macro to make a variable that can hold the chart.
Dim myChart As ChartObject

'Create a chart and put the newly created chart inside of the variable created above.
Set myChart = Worksheets("sheet1").ChartObjects.Add(10, 80, 300, 175)

'Add some basic elements to the chart
myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

End Sub

Title:="Chart Title" put whatever you want inside of the quotation marks for the title of the chart.

Set Category and Value Titles

Sub CreateChart()

'Tell the macro to make a variable that can hold the chart.
Dim myChart As ChartObject

'Create a chart and put the newly created chart inside of the variable created above.
Set myChart = Worksheets("sheet1").ChartObjects.Add(10, 80, 300, 175)

'Add some basic elements to the chart
myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

End Sub

CategoryTitle:="Category Title" the title that goes along the bottom of the chart. Put whatever you want within the quotation marks.

ValueTitle:="Value Title" the title that goes along the left side of the chart. Put whatever you want within the quotation marks.

Include a Legend

Sub CreateChart()

'Tell the macro to make a variable that can hold the chart.
Dim myChart As ChartObject

'Create a chart and put the newly created chart inside of the variable created above.
Set myChart = Worksheets("sheet1").ChartObjects.Add(10, 80, 300, 175)

'Add some basic elements to the chart
myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

End Sub

HasLegend:=True when this is set to True, a legend will appear - also, often times a legend will appear by default. If you want to make sure there is no legend, set this value to False.

Possible Values

True show a legend.

False don't show a legend.

Sometimes legends appear by default; use False when you want to ensure that none will appear.

Change Chart Size and Position

Sub CreateChart()

'Tell the macro to make a variable that can hold the chart.
Dim myChart As ChartObject

'Create a chart and put the newly created chart inside of the variable created above.
Set myChart = Worksheets("sheet1").ChartObjects.Add(10, 80, 300, 175)

'Add some basic elements to the chart
myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

End Sub

ChartObjects.Add(10, 80, 300, 175) the numbers here control the size and position of the chart.

Add(Left, Top, Width, Height)

10 is the position from the left side of the worksheet. You can set this number to whatever you want so that the chart fits into your data nicely.

80 is the position from the top of the worksheet. You can set this number to whatever you want so that the chart fits into your data nicely.

300 is the width of the chart.

175 is the height of the chart.

Play around with these values until you get the desired size and position of the chart in your worksheet. Note that adding a legend to a chart will make it seem smaller as there will be less space for the data to be displayed.

Fewer Arguments

The above example includes a kind of 'interesting' way of writing the arguments for a function in VBA.

myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title", _
    HasLegend:=True

Each argument for the ChartWizard is put onto its own line and follows this format:

Source:= each line begins with the name of the argument and a :=

, _ each line ends with a comma and an underscore.

Last line this is important! The last line for the ChartWizard, should not end with , _ Notice that the last line is this: HasLegend:=True but if you did not want to include the HasLegend argument and wanted it to end with the ValueTitle argument, you would delete the HasLegend argument (entire line) and remove the , _ from the Value Title argument, like this:

myChart.Chart.ChartWizard _
    Source:=Worksheets("Sheet1").Range("A1:E4"), _
    Gallery:=xlLine, _
    Title:="Chart Title", _
    CategoryTitle:="Category Title", _
    ValueTitle:="Value Title"

This may seem confusing at first, but it's standard practice in VBA and Macros.

Notes

There are many ways to add charts to Excel, especially considering new and old versions of the program; however, the example above provides a robust solution that will work across many versions of Excel.

Download the sample file to see the above examples in Excel.