VBA Guide For Charts and Graphs

Associated Files Download Links

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:

The Source Data For the Chart

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:

Creating a Chart using VBA and the ChartObjects method

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:

The Source range for Creating a Pie Chart Using VBA

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:
Specifying the Chart Type in VBA

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:

The Active Chart

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:

Chart with title added using VBA

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:

Active Chart Changing Background Color

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:

Changing the Chart Background Color in VBA

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:
Changing the Chart Background Color in VBA with ColorIndex

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:

Selected Chart For Changing the Plot Area Color

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:
Changing the Plot Area color Using VBA

Adding a Legend Using VBA

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

Selected Chart for Changing the Legend

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:
Adding A Legend to the Chart Using VBA

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:

Pie Chart without Labels

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:

Adding data labels to a Pie Chart in VBA

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:

Column Chart

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 an X-axis and Axis Title Using VBA

Adding a Y-axis and Title in VBA

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

Chart for Y-axis and title

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:

Adding a Y-Axis and Axis Title Using VBA

Changing the Number Format of An Axis

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

Chart Selected For Changing The Number Format

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 Number Format of an Axis Using VBA

Changing the Formatting of the Font in a Chart

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

Source Chart For Formatting in VBA

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:

Changing The Format of the Font of the Entire Chart in VBA

Deleting a Chart Using VBA

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

Chart Source For Delete

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:

Chart Source For Chart Objects

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:

VBA ChartObjects Collection

 

Inserting a Chart on Its Own Chart Sheet

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

Source Range For Chart Sheet

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:
Adding a Chart to Its Own Chart Sheet Using VBA

See some of our other charting tutorials:

Charts in Excel

Create a Bar Chart in VBA