Étiquette : chart

By default, events are associated with charts that are created on separate chart sheets. Let’s look at some examples of handling events related to charts.

Suppose we need to change the color of the chart area and the chart itself (located on a separate chart sheet) depending on where the mouse click occurs. To handle this event, you can use the code shown in the Chart1 module.

Another example concerns handling the mouse move event on chart sheets. Suppose we want to display additional annotations related to data points in a text box located on the chart sheet .

To implement this example, prepare on Sheet1 the corresponding data table and an annotations table. Then, in the Chart1 module, enter the code.

Linking annotation text to a chart sheet (Chart1 module)

Option Explicit
Private Sub Chart_MouseMove(ByVal Button As Long, ByVal Shift As Long, _
    ByVal X As Long, ByVal Y As Long)  
    Dim RowId As Long
    Dim rg1 As Long, rg2 As Long
    Dim MyText As String
    On Error Resume Next
    ActiveChart.GetChartElement X, Y, RowId, rg1, rg2  
    If RowId = xlSeries Then
        MyText = Sheets("Sheet1").Range("Note").Offset(rg2, rg1)
    Else
        MyText = "For information about Nobel Prize winners, " & _
                 "select a column in the chart."
    End If   
    ActiveChart.Shapes(1).TextFrame.Characters.Text = MyText
End Sub

Linking Events to Embedded Charts

If a chart is embedded in a worksheet, you cannot directly link events to it. In this case, you need to perform several additional steps before you can bind the required events to charts located on worksheets.

As a small example, do the following:

• Open the VBA editor and create a class module named MyEventClassModule (Insert | Class Module, then set the Name property to MyEventClassModule).
• Declare in the class module a variable of type Chart with the keyword WithEvents. After that, in the code editor, the object MyEventClassModule will appear in the object list, and all events associated with charts will appear in the event list.

Linking events to embedded charts. Class module MyEventClassModule (version 1)

Public WithEvents MyChartClass As Chart

• In the code editor, enter the code for handling the events needed for your project’s business logic. For example we link the mouse button click to a message: “Think about your next steps!”

Linking events to embedded charts. Class module MyEventClassModule (version 2)

Private Sub MyChartClass_MouseDown(ByVal Button As Long, _
    ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    MsgBox "Think about your next steps!"
End Sub

• Link the event to the embedded chart. For example, this can be done at the workbook open stage by adding to the ThisWorkbook module the code. This associates the first embedded chart on the first worksheet with the MyChartClass object. Now, when you click the mouse on this chart, the message “Think about your next steps!” will appear.

Linking events to embedded charts. ThisWorkbook module

Dim MyClassModule As New MyEventClassModule
Sub ChartInit()
    Set MyClassModule.MyChartClass = Worksheets(1).ChartObjects(1).Chart
End Sub

Private Sub Workbook_Open()
    ChartInit
End Sub

Changing Chart Type via Context Menu

As another example of using chart events for embedded charts, let’s consider a project where, when you right-click the chart, a context menu appears with the following commands: Column, Line, Doughnut, Area, Bar, and Cone. Selecting one of these changes the chart to the corresponding type.

First, the event to handle is the right-click on an embedded chart. Therefore, you need to create a class (in this case ChartEventClass) where the code to handle this event is implemented. When the workbook is opened, the instance of ChartEventClass is linked to the specific chart, the context menu is created, and the commands of this menu are assigned macros that perform the chart type change.

If you want to protect a chart built on a worksheet, as well as the worksheet data outside a certain range, use the Protect method of the Worksheet object with the parameter UserInterfaceOnly set to True. This will protect the worksheet and allow data entry only in the specified cells. For example, to protect all worksheet objects except the range B4:G13, add the code from Listing 6.8 to the ThisWorkbook module.

To allow data entry on the worksheet, go to the Review tab on the ribbon and, in the Changes group, click Unprotect Sheet. Typically, you will be prompted to enter the password (in our case, « pass »).

Setting protection on an embedded chart. ThisWorkbook module

Private Sub Workbook_Open()
    SetPtotection
End Sub

Private Sub SetPtotection()
    On Error Resume Next
    Worksheets("Vedomost").Range("B4:G13").Locked = False
    Worksheets("Vedomost").Protect Password:="pass", UserInterfaceOnly:=True
End Sub

Note
If you add protection to a worksheet that contains controls, attempting to use the controls may cause a project error. Remove the sheet protection and perform the usual actions.

Protecting a Chart on a Separate Chart Sheet

To protect a chart located on a separate chart sheet (a Chart object), use the Protect method:

Protect(Password, DrawingObjects, Contents, Scenarios, UserInterfaceOnly)

• Password — sets the protection password.
• DrawingObjects — protects drawing objects.
• Contents — protects the entire chart.
• Scenarios — protects scenarios.
• UserInterfaceOnly — protects the user interface but not macros. If this parameter is omitted, the protection applies to both the interface and macros.

You can remove protection using Unprotect:

Unprotect(Password)

Listing shows how to protect a chart located on a separate sheet, and also how to lock all cells on worksheet Sheet1 for the user, except the range B4:G13.

Setting protection on a chart. ThisWorkbook module

Private Sub Workbook_Open()
    SetPtotection
End Sub

Private Sub SetPtotection()
    On Error Resume Next
    Charts(1).Protect Password:="d1", UserInterfaceOnly:=True
    Worksheets("Sheet1").Range("B4:G13").Locked = False
    Worksheets("Sheet1").Protect Password:="1"
End Sub

Let us now look at an example of creating a surface chart, which naturally has a three-dimensional orientation. As the data source, we will once again use the report of the computer club network’s performance. The surface will be created automatically from the table when the workbook is opened:

• the x-axis will represent the names of the clubs,
• the y-axis will represent the months,
• the z-axis will represent the clubs’ revenues.

Clearly, the clarity of a surface chart greatly depends on the side and angle from which the user views it. Therefore, in addition to programming the chart creation process, we will place two control elements (lists) on the worksheet.

• The first list is intended for changing the elevation angle from which the surface is viewed. Set its Name property to Elev.
• The second list is intended for rotating the surface around the z-axis. Set its Name property to Rotat.

When the worksheet is activated, the lists are populated with permissible angles. In the ThisWorkbook standard module and the Vedomost worksheet module, enter the code for the corresponding procedures.

As another example, let us consider a surface chart where the rotation takes place in three dimensions.

To implement this example, perform the following steps:

1. Prepare the data range for the chart. Enter values into cells A1:A16 and B1:L1 for the x-axis (interval: –1 to 6.5, step: 0.5) and y-axis (interval: –1 to 1.2, step: 0.2).

In cell B2, enter the formula for z = cos(x)cos(y)sin(xy):

=COS($A2)*COS(B$1)*SIN($A2*B$1)

Copy this formula across the range B2:L16.

• Select the range A1:L16 and build a surface chart using Excel’s built-in tools: go to the Insert tab on the ribbon, in the Charts group click Other Charts, and choose the type Surface.
• Format the resulting surface using the features of the contextual Chart Tools tabs.
• Place three CommandButton controls on the worksheet and set their Caption properties as follows:
  • ROTATION (for CommandButton1),
  • TURN (for CommandButton2),
  • PERSPECTIVE (for CommandButton3).
• Enter in a standard module and in the worksheet module Surface_Rotation the code for the procedures that support surface rotation in three dimensions.

Quite often, it is desirable to see certain trends in the data presented on a chart. In such cases, you can add a trendline, which makes it possible to forecast values.

If you want to add a trendline using Microsoft Excel’s built-in tools, activate the chart, go to the Layout contextual tab on the ribbon, and in the Analysis group click the Trendline drop-down list, then choose the desired type of trendline. Remember that a trendline is always constructed for the selected data series; therefore, if the chart contains multiple series, you need to specify for which series the trendline should be built. Alternatively, you can select the desired series directly on the chart and, from the context menu, choose Add Trendline.

From the VBA perspective, all trendlines corresponding to a given data series form the Trendlines collection, whose elements are Trendline objects. The Trendlines collection has only two methods:

• Add — adds a new element to the collection,
• Item — returns a specific element from the collection.

The Trendline object has the same properties as the parameters of the Add method (see Microsoft VBA Help).

Description of the Add method of the Trendlines collection

Add(Type, Order, Period, Forward, Backward, Intercept, DisplayEquation, DisplayRSquared, Name)

• Type — sets the type of trendline. Valid values:
  • xlLinear (linear),
  • xlLogarithmic (logarithmic),
  • xlExponential (exponential),
  • xlPolynomial (polynomial),
  • xlMovingAvg (moving average),
  • xlPower (power).
• Order — sets the order of the polynomial trendline (valid integers 2 to 6; used only if Type = xlPolynomial).
• Period — trend period (valid integers 1 to the number of data points; used only if Type = xlMovingAvg).
• Forward — number of points forward (future) to forecast.
• Backward — number of points backward (past) to forecast.
• Intercept — intercept on the y-axis.
• DisplayEquation — Boolean, whether to display the trendline equation on the chart.
• DisplayRSquared — Boolean, whether to display the R² (coefficient of determination) value.
• Name — string specifying the name of the trendline.

Example: Trendline in the Computer Club Project

In our last project, we will add a group of CheckBox controls that allow the user to manage the display of the trendline on the chart (Fig. 6.10; see also file 7-Building a Trendline.xlsm on the CD).

On the Vedomost worksheet, add three checkboxes and, in the Properties window, set their properties as shown in Table.

Table. Values of properties set in the Properties window

Control	Property	Value
CheckBox	Name	Trend
	Caption	Trendline
CheckBox	Name	Equation
	Caption	Equation
CheckBox	Name	Coef
	Caption	R-squared value

Open the previous project related to computer clubs and make the following additions. In the Vedomost worksheet module, add the code for the three Click event procedures of the checkboxes:

• The Trendline checkbox builds or removes the trendline. If the trendline is removed, the Equation and R-squared checkboxes are disabled.
• The Equation checkbox controls whether the equation of the trendline is displayed.
• The R-squared checkbox controls whether the coefficient of determination is displayed.

Trendline. Vedomost worksheet module

Private Sub Trend_Click()
    Dim c As Chart
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    ActiveSheet.ChartObjects(1).Activate
    Set c = ActiveChart
    If Trend.Value Then
        Equation.Enabled = True
        Coef.Enabled = True
        c.SeriesCollection(1).Trendlines.Add _
            Type:=xlLinear, Forward:=0, Backward:=0, _
            DisplayEquation:=False, DisplayRSquared:=False
        If Trend.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = False
        End If
        If Coef.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = True
        End If
    Else
        c.SeriesCollection(1).Trendlines(1).Delete
        Equation.Enabled = False
        Coef.Enabled = False
    End If
End Sub

Private Sub Equation_Click()
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    If Trend.Value Then
        Dim c As Chart
        ActiveSheet.ChartObjects(1).Activate
        Set c = ActiveChart
        If Equation.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = True
        Else
            c.SeriesCollection(1).Trendlines(1).DisplayEquation = False
        End If
    End If
End Sub

Private Sub Coef_Click()
    On Error Resume Next
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then Exit Sub
    If Trend.Value Then
        Dim c As Chart
        ActiveSheet.ChartObjects(1).Activate
        Set c = ActiveChart
        If Coef.Value Then
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = True
        Else
            c.SeriesCollection(1).Trendlines(1).DisplayRSquared = False
        End If
    End If
End Sub

In the ThisWorkbook module, add the following procedure to set the initial states of the checkboxes . Naturally, the call to InitTrend must also be added to the Workbook_Open procedure in the same module.

Trendline. ThisWorkbook module

Private Sub InitTrend()
    With Worksheets("Vedomost")
        .Trend.Value = False
        .Equation.Value = False
        .Coef.Value = False
    End With
End Sub

By default, in Microsoft Excel, charts do not display data contained in hidden rows or columns. In this example, we will illustrate a simple way to hide and display data series in a chart. As control elements for visualizing or hiding a data series—both in the worksheet and in the chart we will use checkboxes.

To implement this example, follow these steps:

• Prepare a table on the worksheet showing product sales by months.
• Build a chart for the prepared data: go to the Insert tab on the ribbon, and in the Charts group, choose from the Line list the chart type Line with Markers.
• Format the resulting chart.
• Add five CheckBox controls on the worksheet in sequence and set the Caption property for each of them respectively to: Ruler, Pencil Case, Pen, Pencil, Eraser.
• In the Sheet1 worksheet module, add event-handling procedures for the Click events of the CheckBox controls.

Continuing to enhance the example related to the activity of computer clubs, let us improve the automatic chart construction. Now, the user can add or delete any number of months in the overall report table located on the Vedomost worksheet. After changing the number of months, it is sufficient to click the club list, and the chart will automatically be rebuilt.

To implement this task, modify the Click event procedure of the Club list as shown:

Controlling chart type and legend. Vedomost worksheet module

Private Sub Club_Click()
    Dim r As Integer
    ActiveSheet.ChartObjects(1).Activate
    r = Club.ListIndex + 1
    Dim rgn As Range
    Dim rgnTitle As Range
    Set rgn = Range("A3").CurrentRegion
    Set rgnTitle = rgn.Rows(1)
    Set rgnTitle = rgnTitle.Offset(0, 1)
    Set rgnTitle = rgnTitle.Resize(ColumnSize:=rgnTitle.Columns.Count - 2)
    Set rgn = rgn.Offset(1, 1)
    Set rgn = rgn.Resize(rgn.Rows.Count - 2, rgn.Columns.Count - 2)
    With ActiveChart
        .SetSourceData Source:=rgn.Rows(r), PlotBy:=xlRows
        .SeriesCollection(1).XValues = rgnTitle
    End With
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = Club.Text
    End With
End Sub

Now let’s modify the previous example and add another list on the worksheet so that the user can control both the chart type and the legend display.

• On the Vedomost worksheet, create a second list. In the Properties window, set its Name property to DType.
• In the worksheet module Vedomost, additionally enter the procedure FillDType. In the Workbook_Open event procedure, add a call to FillDType as well.

Controlling the chart type and legend. ThisWorkbook module

Private Sub Workbook_Open()
    DeleteCharts
    ChartBuilder
    FilllstCategory
    FillDType
End Sub

Private Sub FillDType()
    Dim tb(6, 1) As Variant
    tb(0, 0) = "Column":     tb(0, 1) = xlColumnClustered
    tb(1, 0) = "Line":       tb(1, 1) = xlLine
    tb(2, 0) = "Pie":        tb(2, 1) = xlPie
    tb(3, 0) = "Doughnut":   tb(3, 1) = xlDoughnut
    tb(4, 0) = "Area":       tb(4, 1) = xlArea
    tb(5, 0) = "Bar":        tb(5, 1) = xlBarClustered
    tb(6, 0) = "Cone":       tb(6, 1) = xlConeColStacked
    With Worksheets("Vedomost").DType
        .ColumnCount = 2
        .TextColumn = 2
        .ColumnWidths = "70;0"
        .List = tb
        .ListIndex = 0
    End With
End Sub

• In the Vedomost worksheet module, enter the following event-handling procedure for the Click event of the list. This will rebuild the chart whenever a chart type is selected (Listing 6.4).

Selecting the chart type. Vedomost worksheet module

Private Sub DType_Click()
    ActiveSheet.ChartObjects(1).Activate
    ActiveChart.ChartType = DType.Text
    If DType.Text = xlPie Or DType.Text = xlDoughnut Then
        ActiveChart.HasLegend = True
    Else
        ActiveChart.HasLegend = False
    End If
End Sub

Notes

• Two-column list design. Charts have both names (e.g., “Column”, “Pie”) and constants (e.g., xlColumnClustered, xlPie) that define chart types. The list should display only the chart type names, but the result of the selection must be the constant. To solve this, the code creates a two-column list:
  • the first column contains the names of the chart types,
  • the second column contains the constants defining the chart types.
    Since we don’t need to display the second column, its width is set to zero. However, because the list’s TextColumn property is set to 2, the selected item’s Text property returns the constant (second column), not the name.
• Legend necessity. Depending on the chart type, the legend may be necessary or redundant. For example:
  • For a column chart, the legend is unnecessary in the built application because the chart already includes clear labels.
  • For pie or doughnut charts, the legend is indispensable.
    Therefore, in the DType_Click procedure, the legend is either shown or hidden depending on the selected chart type.

Let’s consider a report sheet summarizing the performance of a network of computer clubs, placed on the worksheet Vedomost in an Excel workbook. When the workbook opens, there should be a chart next to the data that provides a visual representation of the performance dynamics of a specific club. This must be achieved by allowing the user to select a particular club from a list of clubs. Moreover, selecting a club from the list should change the data range on which the chart is based and trigger the chart to update accordingly.

To implement this project, perform the following steps.

• Assign, for example, the name Vedomost to the first worksheet.
• On this worksheet, prepare a tabular report summarizing the performance results of the computer clubs.
• Create a list on the worksheet and, using the Properties window, set its Name property to Club.
• In the ThisWorkbook module, enter the required code: when the workbook opens, the Workbook_Open procedure runs, which deletes all charts from the Vedomost worksheet, builds a 3-D column chart based on the performance results of the “Altair” computer club, and positions it so that it occupies the range G7:P26. This ensures that the chart does not overlap the data table. The procedure also populates the list based on the header row of the data table and selects the first item in this list.
• In the code module of the Vedomost worksheet, enter an event procedure to handle the list’s Click event so that when a club is selected from the list, the chart is rebuilt.

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

In MS Excel, you can create different chart types and format them appropriately. From the VBA point of view, the workbook’s Sheets collection includes two families of sheets: Worksheets (worksheets) and Charts (chart sheets). The Charts family contains charts created on chart sheets. This family does not include charts embedded directly on worksheets. Such charts belong to the ChartObjects family. Thus, a ChartObject is embedded in a Worksheet, whereas a Chart is embedded in a Workbook.

The Workbook and Application objects share the ActiveChart property, which returns the active chart in the workbook, regardless of which family it belongs to. The Chart object has a number of child objects listed in Table.

Table. Objects subordinate to the Chart object

Object	Description
ChartArea	The area in which the chart is drawn
PlotArea	The chart plotting area
Floor	The horizontal plane (floor) of a 3-D chart
Walls (BackWall, Walls)	The vertical planes (walls) of a 3-D chart
Corners	The corners of a 3-D chart
PageSetup	Page setup parameters
ChartTitle	The chart title
SeriesCollection	The range of data plotted on the value (y) axis
Trendlines	Trendline(s)
Axis	Chart axes
AxisTitle	Axis titles
DisplayUnitLabel	Axis display unit label
Gridlines	Gridlines
TickLabels	Tick labels on the axes
DataTable	The chart’s data table
Legend	The legend
Shapes	The drawing shapes within the chart
SeriesCollection	Data series
DataLabels	Data labels
Points	Data points

If a chart is located on a worksheet, then the object hierarchy—for example, to address the chart title—can be represented as:

Application

  Workbook

    Worksheet

      ChartObject

        Chart

          ChartTitle

For charts that are on chart sheets, the object hierarchy is slightly different:

Application

  Workbook

    Chart

      ChartTitle

Adding a new element to the ChartObjects and Charts families

The ChartObjects and Charts families have the methods Add (create a new family element) and Delete (remove a family element), and the Count property (return the number of elements in the family).

Add method of the ChartObjects family:

Add(Left, Top, Width, Height)

• Left, Top — set the coordinates on the worksheet of the chart’s upper-left corner.
• Width, Height — set the chart’s width and height.
  All parameters are optional.

Add method of the Charts family:

Add(Before, After, Count)

• Before — specifies before which sheet the chart is added.
• After — specifies after which sheet the chart is added.
• Count — specifies how many charts to add.
  All parameters are also optional.

Properties of the Chart object

The Chart object has more than 50 properties that determine the chart’s appearance (see Excel Help for detailed descriptions). The main properties are shown in Table, and the main chart types (values of the ChartType property) are shown in Table.

Table. Key properties of the Chart object

Property	Description
Area3DGroup	Returns a ChartGroup object encapsulating information about a 3-D area
AutoScaling	Enables automatic scaling for 3-D charts
Bar3DGroup	Returns a ChartGroup object for a 3-D bar chart
ChartArea	Returns a ChartArea object
ChartTitle	Returns a ChartTitle object
ChartType	Sets the chart type (valid values in Table 6.4)
Column3DGroup	Returns a ChartGroup object for 3-D columns
Corners	Returns a Corners object
DataTable	Returns a DataTable object
DepthPercent	Sets the depth percentage for a 3-D chart
DisplayBlanksAs	Specifies how empty cells are interpreted: xlNotPlotted, xlInterpolated, xlZero
Elevation	Sets the viewing angle for a 3-D chart
Floor	Returns a Floor object
GapDepth	Sets the gap between series in a 3-D chart
HasAxis	Specifies whether the chart has axes
HasDataTable	Specifies whether the chart has a data table
HasLegend	Checks whether the chart has a legend
HasTitle	Checks whether the chart has a title
HeightPercent	Sets chart height as a percentage of its width
Hyperlinks	Returns the Hyperlinks collection
Index	Returns the index value within Charts
Legend	Returns a Legend object
PageSetup	Returns a PageSetup object
Perspective	Sets perspective for a 3-D chart
PlotArea	Returns a PlotArea object
PlotBy	Defines how data is laid out: xlColumns or xlRows
PlotVisibleOnly	Specifies whether to include hidden cells
ProtectContents, ProtectData, ProtectDrawingObjects, ProtectFormatting, ProtectionSelection, ProtectGoalSeek	Boolean properties indicating whether protection is enabled for the corresponding chart element
Rotation	Returns the rotation angle of a 3-D chart around the z-axis
Visible	Controls chart visibility
Walls	Returns a Walls object

Table. Valid values of the ChartType property

Type of Chart	Constants (in English)
Column Chart	xlColumnClustered, xl3DColumnClustered, xlColumnStacked, xl3DColumnStacked, xlColumnStacked100, xl3DColumnStacked100, xl3DColumn
Bar Chart	xlBarClustered, xl3DBarClustered, xlBarStacked, xl3DBarStacked, xlBarStacked100, xl3DBarStacked100
Line Chart	xlLine, xlLineMarkers, xlLineStacked, xlLineMarkersStacked, xlLineStacked100, xlLineMarkersStacked100, xl3DLine
Pie Chart	xlPie, xlPieExploded, xl3DPie, xl3DPieExploded, xlPieOfPie, xlBarOfPie
XY Scatter Chart	xlXYScatter, xlXYScatterSmooth, xlXYScatterSmoothNoMarkers, xlXYScatterLines, xlXYScatterLinesNoMarkers
Area Chart	xlArea, xl3DArea, xlAreaStacked, xl3DAreaStacked, xlAreaStacked100, xl3DAreaStacked100
Doughnut Chart	xlDoughnut, xlDoughnutExploded
Radar Chart	xlRadar, xlRadarMarkers, xlRadarFilled
Surface Chart	xlSurface, xlSurfaceTopView, xlSurfaceWireframe, xlSurfaceTopViewWireframe
Bubble Chart	xlBubble, xlBubble3DEffect
Stock Chart	xlStockHLC, xlStockVHLC, xlStockOHLC, xlStockVOHLC
Cylinder Chart	xlCylinderColClustered, xlCylinderBarClustered, xlCylinderColStacked, xlCylinderBarStacked, xlCylinderColStacked100, xlCylinderBarStacked100, xlCylinderCol
Cone Chart	xlConeColClustered, xlConeBarClustered, xlConeColStacked, xlConeBarStacked, xlConeColStacked100, xlConeBarStacked100, xlConeCol
Pyramid Chart	xlPyramidColClustered, xlPyramidBarClustered, xlPyramidColStacked, xlPyramidBarStacked, xlPyramidColStacked100, xlPyramidBarStacked100, xlPyramidCol

Methods of the Chart object

Like any object, Chart has methods to control its appearance and behavior. The methods most useful in practice are shown in Table.

Table. Methods of the Chart object

Method	Description
Activate	Activates the chart
ApplyDataLabels	Applies specified data labels
AutoFormat	Applies autoformat
Axes	Returns the Axes collection for setting axis properties
ChartObjects	Returns the ChartObjects collection
ChartWizard	Creates/sets up a chart using the wizard
CheckSpelling	Checks spelling
Copy	Copies the chart to the specified location
CopyPicture	Copies the chart to the clipboard as a picture
Delete	Deletes the chart
Deselect	Clears selection from the chart
Export	Exports the chart to a graphics file
GetChartElement	Returns information about the chart element at a specified point
Location	Sets the chart location
Move	Moves the chart
Paste	Pastes chart data from the clipboard
PrintOut	Prints the chart
SendToBack	Sends the chart behind other objects
Protect	Sets protection
Refresh	Refreshes the chart
SaveAs	Saves the modified chart to a new file
Select	Selects the chart
SeriesCollection	Returns the series collection
SetBackgroundPicture	Sets a background picture
SetSourceData	Specifies the source range for the chart
Unprotect	Removes protection from the chart

Events of the Chart object

The Chart object also exposes a number of events (Table 6.6) that allow you to track various user actions.

Table. Events of the Chart object

Event	Description
Activate	Occurs when the chart is activated
BeforeDoubleClick	Occurs before a double-click
BeforeRightClick	Occurs before a right-click
Calculate	Occurs when data change
Deactivate	Occurs when the chart is deactivated
DragOver	Occurs when a range is dragged over the chart
DragPlot	Occurs when a range is dragged and dropped into the chart
MouseDown, MouseUp	Occur when the user presses/releases any mouse button
MouseMove	Occurs when the user moves the mouse pointer over the chart
Resize	Occurs when the chart is resized
Select	Occurs when a chart element is selected
SeriesChange	Occurs when the reference to a data series changes