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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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.

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:

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:

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:

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

 

 

You may also like some of this related content...

Advertisements
Automate Excel
Left Menu Icon