Managing Charts in Excel VBA

To visualize numeric data in Microsoft Excel, there is a wide range of chart types available:

■ Charts
■ Shapes
■ WordArt
■ Conditional Formatting
■ Sparklines
■ SmartArt

Charts can be quickly generated in Excel by simply pressing a button. In this chapter, you’ll learn how to create and modify charts using VBA code. This enables you to use your programming skills to create customized charts.

Naturally, to build a chart, you must first prepare a data range, determine the chart type, consider the elements to display, etc. This chapter demonstrates how to use the Chart and ChartObject objects, which allow you to automate chart creation and configuration.

What Should You Know About Charts?

In Excel, you can create two types of charts: embedded charts and chart sheets.

• Embedded charts appear on a worksheet alongside data and text—ideal for reports.
• Charts on chart sheets are better suited for slide presentations or printing.

Creating a chart takes just a click: select the prepared data, go to the Insert tab, and choose the chart type from the Charts group. By default, the chart appears next to the data.

About the ChartObjects and Charts Collections, ChartObject and Chart

In VBA, the Sheets collection includes two subcollections:

• Worksheets: regular worksheets
• Charts: chart sheets only

Charts embedded in worksheets belong to the ChartObjects collection. Charts on dedicated chart sheets belong to the Charts collection. So:

• ChartObject → embedded in a Worksheet
• Chart → embedded in a Workbook

Both Workbook and Application objects have an ActiveChart property that returns the currently active chart.

Table: Sub-objects of the Chart Object

Adding Items to ChartObjects and Charts Collections

Both collections support:

• Add: add a new chart
• Delete: remove a chart
• Count: number of charts

ChartObjects.Add(left, top, width, height)

• All parameters are optional.
• Defines the location and size of an embedded chart on the worksheet.

Charts.Add(before, after, count)

• All parameters are optional.
• Specifies where to insert a chart sheet in the workbook.

Key Properties of the Chart Object

ChartType Values

Chart Object Methods

Chart Object Events

Creating a Chart

Creating a Simple Chart

You can create a chart on a dedicated chart sheet or embed it in a worksheet. Both are covered below.

Creating a Chart Sheet

Sub CreateChartSheet()
    ThisWorkbook.Charts.Add After:=Worksheets("Sheet1")
    With ActiveChart
        .ChartType = xlLine
        .SetSourceData Worksheets("Sheet1").Range("A1:C11")
        .Name = "Chart1"
    End With
End Sub

Comments:

• Charts.Add creates a new chart sheet.
• ChartType sets the chart type (here: line chart).
• SetSourceData sets the data range for the chart.
• Name assigns a name to the chart.

Note: SetSourceData also accepts a second argument to specify whether data is plotted by rows or columns: xlColumns (default) or xlRows.

Creating an Embedded Chart

Sub CreateEmbeddedChart()
    Dim ChartFrame As ChartObject
    Dim RealChart As Chart

    Set ChartFrame = ThisWorkbook.Worksheets("Sheet1") _
        .ChartObjects.Add(250, 15, 300, 150)
    Set RealChart = ChartFrame.Chart
    RealChart.ChartType = xlLine
    RealChart.SetSourceData Worksheets("Sheet1").Range("A1:C11")
End Sub

Comments:

• ChartObjects.Add(left, top, width, height) creates an embedded chart.
• ChartObject holds the frame; .Chart gives access to the actual chart.
• ChartType and SetSourceData work just like with chart sheets.

Customizing a Chart

You can modify many properties depending on the chart type.

Customize the Chart Sheet

Sub CustomizeChartSheet()
    Dim RealChart As Chart
    Set RealChart = ThisWorkbook.Charts(1)
    CustomizeChart RealChart
End Sub

Comments
The program is divided into two parts. First, the part that applies only to chart sheets:

• A variable of type Chart is declared.
• The first chart sheet in the workbook is assigned to it.
• The CustomizeChart procedure is called, and the chart is passed as a parameter.

Sub CustomizeChart (RealChart As Chart)

Sub CustomizeChart(RealChart As Chart)
    ' Chart Area
    RealChart.ChartArea.Interior.Color = vbCyan
    ' Plot Area
    RealChart.PlotArea.Interior.Color = vbYellow
    ' Title
    RealChart.HasTitle = True
    RealChart.ChartTitle.Text = "Temperature"
    ' Legend
    RealChart.HasLegend = True
    With RealChart.Legend
        .Interior.Color = vbYellow
        .Border.Color = vbBlue
        .Border.Weight = xlThick
    End With
    ' Category Axis
    With RealChart.Axes(xlCategory)
        .HasTitle = True
        .AxisTitle.Text = " Date "
        .TickLabels.NumberFormatLocal = "DD.MM."
    End With
    ' Value Axis
    With RealChart.Axes(xlValue)
        .HasTitle = True
        .AxisTitle.Text = " Degree"
        .MinimumScale = 5
        .MaximumScale = 35
    End With
    ' Data Series
    With RealChart.SeriesCollection(1)
        .Border.Color = vbRed
        .MarkerStyle = xlMarkerStyleCircle
        .MarkerForegroundColor = vbRed
        .MarkerBackgroundColor = vbRed
    End With
    ' Data Point
    With RealChart.SeriesCollection(1).Points(3)
        .Border.Color = vbBlue
        .ApplyDataLabels xlShowValue
        .MarkerStyle = xlMarkerStyleSquare
        .MarkerForegroundColor = vbBlue
        .MarkerBackgroundColor = vbBlue
    End With
End Sub

Comments

• The ChartArea property represents the entire chart area (plot, title, legend).
• The Interior.Color property sets the fill color.
• PlotArea represents the data plotting area. It also supports color fill.
• The chart title is activated with HasTitle = True.
• ChartTitle.Text sets the title string.
• Legend is controlled via HasLegend. You can style its interior and border.
• All axes are listed in Axes.
  • xlCategory: horizontal axis
  • xlValue: vertical axis
• AxisTitle.Text defines the title for each axis.
• TickLabels.NumberFormatLocal sets the format (e.g. for dates).
• MinimumScale and MaximumScale adjust the axis scale range.
• SeriesCollection(1) accesses the first data series:
  • You can style its border and marker (color, shape).
• Points(3) accesses the third data point in the series:
  • Its appearance and label are customized.
• ApplyDataLabels xlShowValue displays value labels.
• Valid constants for marker styles include:
  • xlMarkerStyleCircle, xlMarkerStyleSquare, xlMarkerStyleNone.

Customize an Embedded Chart

Sub CustomizeEmbeddedChart()
    Dim CO As ChartObject
    Dim CH As Chart

    Set CO = ThisWorkbook.Worksheets("Sheet1").ChartObjects(1)
    CO.Left = 180
    CO.Top = 25
    CO.Width = 390
    CO.Height = 250

    Set CH = CO.Chart
    CustomizeChart CH
End Sub

Comments

• Variables CO and CH are declared as ChartObject and Chart.
• For embedded charts, you can adjust position and size using:
  • Left, Top, Width, Height
• The known CustomizeChart procedure is called to apply styling.