Building a Chart with Excel VBA

Now let’s look at an example of creating a chart with VBA. On a worksheet of the Excel workbook there are four buttons: Create Chart, Delete Chart, Add Data Labels, Delete Data Labels. When you click Create Chart, a chart is created on the active worksheet, and its title will match the contents of cell B1.

The Delete Chart button removes the chart. The remaining two buttons allow you to add or remove a series of data labels on the created chart, respectively.

Place four CommandButton controls on the worksheet. Note that on the Developer tab of the ribbon, in the Controls group, the Design Mode button is active.

For the Caption property of the added buttons (select a button and use the Properties command in the Controls group on the Developer tab), enter the following values respectively: Create Chart, Delete Chart, Add Data Labels, and Delete Data Labels. If you wish, also change the Font property.

Successively click the added buttons and add program code to the Sheet1 module according to:

Building a chart. Sheet1 module

Private Sub CommandButton1_Click()
    ChartCreate
End Sub

Private Sub CommandButton2_Click()
    ChartDelete
End Sub

Private Sub CommandButton3_Click()
    DataLabAdd
End Sub

Private Sub CommandButton4_Click()
    DataLabDelete
End Sub

Thus, clicking the corresponding buttons should run the following procedures: ChartCreate — add a chart to the worksheet; ChartDelete — remove the chart from the worksheet; DataLabAdd — add data labels to the chart; DataLabDelete — remove data labels from the chart.

To implement these procedures, add a standard module in the VBA editor (Insert | Module) and enter the relevant code.

Note that the ChartCreate procedure for adding a chart is implemented as follows. A new chart is created with the Add method. The ChartType property sets the chart type. The SetSourceData method provides a reference to the range whose values are plotted on the value (Y) axis. In this case, it is the range B2:B12 of the active worksheet. The SeriesCollection method sets a reference to the range whose values are plotted on the category (X) axis. In our case, this is the range A2:A12 of the active worksheet. Then the Location method specifies the chart location; here it will be embedded on the worksheet with the specified name, which matches the contents of cell B1. After that, the chart’s elements are defined. Using the ChartTitle property and the Axes method, we set the title (which matches the worksheet name) and the axis titles (note that the method .Axes(xlSeries).Delete removes data labels for the second axis located at the base of the chart). Then HasLegend removes the legend, after which properties are set to format the walls, floor, data series, and the plot area. The Top, Left, Width, and Height properties position the chart at a specified place on the worksheet. Thus, this procedure allows you to build a chart on any worksheet.

Deleting the chart is done with ChartObjects.Delete.

The DataLabAdd procedure for adding data labels to the chart is based on the ApplyDataLabels method for SeriesCollection(1). In turn, the DataLabDelete procedure sets HasDataLabels = False to remove data labels.

Building a chart. Standard module

' Procedure for building a chart
Sub ChartCreate()
    Dim rx As Range
    Dim ry As Range
    Dim nameX As String
    Dim nameY As String
    Dim title As String
    Dim nameSh As String
    nameX = "Volume"
    nameY = "Year"
    nameSh = ActiveSheet.Name
    title = Sheets(nameSh).Range("B1")
    Set ry = Sheets(nameSh).Range("B2:B12")
    Set rx = Sheets(nameSh).Range("A2:A12")
    ' Add a chart
    Charts.Add
    ActiveChart.ChartType = xlCylinderCol
    ActiveChart.SetSourceData Source:=ry, PlotBy:=xlColumns
    ActiveChart.SeriesCollection(1).XValues = _
        "=" & rx.Address(ReferenceStyle:=xlR1C1, external:=True)
    ActiveChart.Location Where:=xlLocationAsObject, Name:=nameSh
    ' Define chart elements
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = title
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = nameX
        .Axes(xlValue,   xlPrimary).HasTitle = True
        .Axes(xlValue,   xlPrimary).AxisTitle.Characters.Text = nameY
        .Axes(xlSeries).Delete
    End With
    ActiveChart.HasLegend = False
    ' Format the back wall
    With ActiveChart.BackWall.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.150000006
        .Transparency = 0
        .Solid
    End With
    ' Format the side walls

    With ActiveChart.Walls.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.050000007
        .Transparency = 0
        .Solid
    End With
    ' Format the floor
    With ActiveChart.Floor.Format.Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorBackground1
        .ForeColor.TintAndShade = 0
        .ForeColor.Brightness = -0.5
        .Transparency = 0
        .Solid
    End With
    ' Format the data series (3-D bevel)
    With ActiveChart.SeriesCollection(1).Format.ThreeD
        .BevelTopType = msoBevelCoolSlant
        .BevelTopInset = 13
        .BevelTopDepth = 6
    End With
    ' Format the plot area (shape fill)
    With Worksheets(nameSh).Shapes("Диаграмма 1").Fill
        .Visible = msoTrue
        .ForeColor.ObjectThemeColor = msoThemeColorAccent1
        .ForeColor.TintAndShade = 0.3399999738
        .ForeColor.Brightness = 0
        .BackColor.ObjectThemeColor = msoThemeColorAccent1
        .BackColor.TintAndShade = 0.7649999857
        .BackColor.Brightness = 0
        .TwoColorGradient msoGradientHorizontal, 1
    End With
    ' Position the chart on the worksheet
    With Worksheets(nameSh).ChartObjects(1)
        .Top = Range("G5").Top
        .Left = Range("G5").Left
        .Width = Range("G1:R34").Width
        .Height = Range("C1:R34").Height
    End With
End Sub

' Procedure for deleting the chart
Sub ChartDelete()
    ActiveSheet.ChartObjects.Delete
End Sub

' Procedure for adding data labels to the chart
Sub DataLabAdd()
    Dim Rng As Range
    Dim Ct As Chart
    Dim i As Integer, K As Integer
    ' Identify the chart
    Set Ct = ActiveSheet.ChartObjects(1).Chart
    ' Prompt for the range to use as data labels
    On Error Resume Next
    Set Rng = Application.InputBox( _
        prompt:="Enter the range for the series' data labels", Type:=8)
    If Rng Is Nothing Then Exit Sub
    On Error GoTo 0
    ' Add data labels
    Ct.SeriesCollection(1).ApplyDataLabels _
        Type:=xlDataLabelsShowValue, AutoText:=True, LegendKey:=False
    ' Identify points and assign labels
    K = Ct.SeriesCollection(1).Points.Count
    For i = 1 To K
        Ct.SeriesCollection(1).Points(i).DataLabel.Text = _
            "=" & "'" & Rng.Parent.Name & "'!" & _
            Rng(i).Address(ReferenceStyle:=xlR1C1)
    Next i
End Sub

' Procedure for deleting data labels from the chart
Sub DataLabDelete()
    Dim Ct As Chart
    Set Ct = ActiveSheet.ChartObjects(1).Chart
    Ct.SeriesCollection(1).HasDataLabels = False
End Sub