VBA Guide For Charts and Graphs

Excel charts and graphs are used to visually display data. In this tutorial, we are going to cover how to use VBA to create and manipulate charts and chart elements.

You can create embedded charts in a worksheet or charts on their own chart sheets.

Creating an Embedded Chart Using VBA

We have the range A1:B4 which contains the source data, shown below:

You can create a chart using the ChartObjects.Add method. The following code will create an embedded chart on the worksheet:

Sub CreateEmbeddedChartUsingChartObject()

Dim embeddedchart As ChartObject

Set embeddedchart = Sheets("Sheet1").ChartObjects.Add(Left:=180, Width:=300, Top:=7, Height:=200)
embeddedchart.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B4")

End Sub

The result is:

You can also create a chart using the Shapes.AddChart method. The following code will create an embedded chart on the worksheet:

Sub CreateEmbeddedChartUsingShapesAddChart()

Dim embeddedchart As Shape

Set embeddedchart = Sheets("Sheet1").Shapes.AddChart
embeddedchart.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B4")

End Sub

Specifying a Chart Type Using VBA

We have the range A1:B5 which contains the source data, shown below:

You can specify a chart type using the ChartType Property. The following code will create a pie chart on the worksheet since the ChartType Property has been set to xlPie:

Sub SpecifyAChartType()

Dim chrt As ChartObject

Set chrt = Sheets("Sheet1").ChartObjects.Add(Left:=180, Width:=270, Top:=7, Height:=210)
chrt.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A1:B5")
chrt.Chart.ChartType = xlPie

End Sub

The result is:

These are some of the popular chart types that are usually specified, although there are others:

• xlArea
• xlPie
• xlLine
• xlRadar
• xlXYScatter
• xlSurface
• xlBubble
• xlBarClustered
• xlColumnClustered

Adding a Chart Title Using VBA

We have a chart selected in the worksheet as shown below:

You have to add a chart title first using the Chart.SetElement method and then specify the text of the chart title by setting the ChartTitle.Text property.

The following code shows you how to add a chart title and specify the text of the title of the Active Chart:

Sub AddingAndSettingAChartTitle()

ActiveChart.SetElement (msoElementChartTitleAboveChart)
    ActiveChart.ChartTitle.Text = "The Sales of the Product"
    
End Sub

The result is:

Note: You must select the chart first to make it the Active Chart to be able to use the ActiveChart object in your code.

Changing the Chart Background Color Using VBA

We have a chart selected in the worksheet as shown below:

You can change the background color of the entire chart by setting the RGB property of the FillFormat object of the ChartArea object. The following code will give the chart a light orange background color:

Sub AddingABackgroundColorToTheChartArea()

ActiveChart.ChartArea.Format.Fill.ForeColor.RGB = RGB(253, 242, 227)

End Sub

The result is:

You can also change the background color of the entire chart by setting the ColorIndex property of the Interior object of the ChartArea object. The following code will give the chart an orange background color:

Sub AddingABackgroundColorToTheChartArea()

ActiveChart.ChartArea.Interior.ColorIndex = 40

End Sub

The result is:

Note: The ColorIndex property allows you to specify a color based on a value from 1 to 56, drawn from the preset palette, to see which values represent the different colors, click here.

Changing the Chart Plot Area Color Using VBA

We have a chart selected in the worksheet as shown below:

You can change the background color of just the plot area of the chart, by setting the RGB property of the FillFormat object of the PlotArea object. The following code will give the plot area of the chart a light green background color:

Sub AddingABackgroundColorToThePlotArea()

ActiveChart.PlotArea.Format.Fill.ForeColor.RGB = RGB(208, 254, 202)
    
End Sub

The result is:

Adding a Legend Using VBA

We have a chart selected in the worksheet, as shown below:

You can add a legend using the Chart.SetElement method. The following code adds a legend to the left of the chart:

Sub AddingALegend()

ActiveChart.SetElement (msoElementLegendLeft)

End Sub

The result is:

You can specify the position of the legend in the following ways:

• msoElementLegendLeft – displays the legend on the left side of the chart.
• msoElementLegendLeftOverlay – overlays the legend on the left side of the chart.
• msoElementLegendRight – displays the legend on the right side of the chart.
• msoElementLegendRightOverlay – overlays the legend on the right side of the chart.
• msoElementLegendBottom – displays the legend at the bottom of the chart.
• msoElementLegendTop – displays the legend at the top of the chart.

Adding Data Labels Using VBA

We have a chart selected in the worksheet, as shown below:

You can add data labels using the Chart.SetElement method. The following code adds data labels to the inside end of the chart:

Sub AddingADataLabels()

ActiveChart.SetElement msoElementDataLabelInsideEnd

End Sub

The result is:

You can specify how the data labels are positioned in the following ways:

• msoElementDataLabelShow – display data labels.
• msoElementDataLabelRight – displays data labels on the right of the chart.
• msoElementDataLabelLeft – displays data labels on the left of the chart.
• msoElementDataLabelTop – displays data labels at the top of the chart.
• msoElementDataLabelBestFit – determines the best fit.
• msoElementDataLabelBottom – displays data labels at the bottom of the chart.
• msoElementDataLabelCallout – displays data labels as a callout.
• msoElementDataLabelCenter – displays data labels on the center.
• msoElementDataLabelInsideBase – displays data labels on the inside base.
• msoElementDataLabelOutSideEnd – displays data labels on the outside end of the chart.
• msoElementDataLabelInsideEnd – displays data labels on the inside end of the chart.

Adding an X-axis and Title in VBA

We have a chart selected in the worksheet, as shown below:

You can add an X-axis and X-axis title using the Chart.SetElement method. The following code adds an X-axis and X-axis title to the chart:

Sub AddingAnXAxisandXTitle()

With ActiveChart
.SetElement msoElementPrimaryCategoryAxisShow
.SetElement msoElementPrimaryCategoryAxisTitleHorizontal
End With


End Sub

The result is:

Adding a Y-axis and Title in VBA

We have a chart selected in the worksheet, as shown below:

You can add a Y-axis and Y-axis title using the Chart.SetElement method. The following code adds an Y-axis and Y-axis title to the chart:

Sub AddingAYAxisandYTitle()

With ActiveChart
.SetElement msoElementPrimaryValueAxisShow
.SetElement msoElementPrimaryValueAxisTitleHorizontal
End With
End Sub

The result is:

Changing the Number Format of An Axis

We have a chart selected in the worksheet, as shown below:

You can change the number format of an axis. The following code changes the number format of the y-axis to currency:

Sub ChangingTheNumberFormat()

ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "$#,##0.00"

End Sub

The result is:

Changing the Formatting of the Font in a Chart

We have the following chart selected in the worksheet as shown below:

You can change the formatting of the entire chart font, by referring to the font object and changing its name, font weight, and size. The following code changes the type, weight and size of the font of the entire chart.

Sub ChangingTheFontFormatting()

With ActiveChart


.ChartArea.Format.TextFrame2.TextRange.Font.Name = "Times New Roman"
.ChartArea.Format.TextFrame2.TextRange.Font.Bold = True
.ChartArea.Format.TextFrame2.TextRange.Font.Size = 14

End With

The result is:

Deleting a Chart Using VBA

We have a chart selected in the worksheet, as shown below:

We can use the following code in order to delete this chart:

Sub DeletingTheChart()

ActiveChart.Parent.Delete

End Sub

Referring to the ChartObjects Collection

You can access all the embedded charts in your worksheet or workbook by referring to the ChartObjects collection. We have two charts on the same sheet shown below:

We will refer to the ChartObjects collection in order to give both charts on the worksheet the same height, width, delete the gridlines, make the background color the same, give the charts the same plot area color and make the plot area line color the same color:

Sub ReferringToAllTheChartsOnASheet()

Dim cht As ChartObject

For Each cht In ActiveSheet.ChartObjects
cht.Height = 144.85
cht.Width = 246.61

cht.Chart.Axes(xlValue).MajorGridlines.Delete
cht.Chart.PlotArea.Format.Fill.ForeColor.RGB = RGB(242, 242, 242)
cht.Chart.ChartArea.Format.Fill.ForeColor.RGB = RGB(234, 234, 234)
cht.Chart.PlotArea.Format.Line.ForeColor.RGB = RGB(18, 97, 172)

Next cht

End Sub

The result is:

 

Inserting a Chart on Its Own Chart Sheet

We have the range A1:B6 which contains the source data, shown below:

You can create a chart using the Charts.Add method. The following code will create a chart on its own chart sheet:

Sub InsertingAChartOnItsOwnChartSheet()

Sheets("Sheet1").Range("A1:B6").Select
Charts.Add

End Sub

The result is:

See some of our other charting tutorials:

Charts in Excel

Create a Bar Chart in VBA