In Excel 2007, a ChartObject is a special type of Shape object. Therefore, it's a member of the Shapes collection. To create a new chart, use the AddChart method of the Shapes collection. The following statement creates an empty embedded chart:

ActiveSheet.Shapes.AddChart

The AddChart method can use five arguments (all are optional):

In many cases, you may find it more efficient to create an object variable when the chart is created. The following procedure creates a line chart that can be referenced in code by using the MyChart object variable:

Sub CreateChart()
    Dim MyChart As Chart
    Set MyChart = ActiveSheet.Shapes.AddChart(xlLineMarkers).Chart
End Sub

A chart without data is not very useful, so you'll want to use the SetSourceData method to add data to a newly created chart. The procedure that follows demonstrates the SetSourceData method. This procedure creates the chart shown in Figure 15-1.

Sub CreateChart()
    Dim MyChart As Chart
    Dim DataRange As Range
    Set DataRange = ActiveSheet.Range("A1:C7")
    Set MyChart = ActiveSheet.Shapes.AddChart.Chart
    MyChart.SetSourceData Source:=DataRange
End Sub

create a chart.xlsm.

Sub CreateChart2()
    Dim MyChart As Chart
    Dim DataRange As Range
    Set DataRange = ActiveSheet.Range("A1:C7")
    Set MyChart = ActiveSheet.ChartObjects.Add(10, 10, 354, 210).Chart
    MyChart.SetSourceData Source:=DataRange
    MyChart.ChartType = xlColumnClustered
End Sub

Figure 15-1. A few lines of VBA code created this chart.

The preceding section describes the basic procedures for creating an embedded chart. To create a chart on a chart sheet, use the Add method of the Charts collection. The Add method of the Charts collection uses several optional arguments, but these arguments specify the position of the chart sheet — not chart–related information.

The example that follows creates a chart on a chart sheet and specifies the data range and chart type:

Sub CreateChartSheet()
    Dim MyChart As Chart
    Dim DataRange As Range
    Set DataRange = ActiveSheet.Range("A1:C7")
    Set MyChart = Charts.Add
    MyChart.SetSourceData Source:=DataRange
    ActiveChart.ChartType = xlColumnClustered
End Sub

I used output from the macro recorder as the basis for the FormatChart procedure shown here, which converts the active chart to a clustered column chart (using Chart Tools

Sub FormatChart()
    If ActiveChart Is Nothing Then Exit Sub
    With ActiveChart
        .ChartType = xlColumnClustered
        .ApplyLayout 3
        .ChartStyle = 28
        .SetElement msoElementPrimaryValueGridLinesNone
        .ClearToMatchStyle
   End With
End Sub

Figure 15-2 shows a chart before and after executing the FormatChart macro.

Figure 15-2. A chart, before and after being formatted.

Note the following points about the FormatChart procedure:

As I've noted, the macro recorder in Excel 2007 ignores many formatting commands when working with a chart. This deficiency is especially irksome if you're trying to figure out how to apply some of the new formatting options such as shadows, beveling, and gradient fills.

In this section, I provide some examples of chart formatting. I certainly don't cover all the options, but it should be sufficient to help you get started so that you can explore these features on your own. These examples assume an object variable named MyChart, created as follows:

Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects(1).Chart

If you apply these examples to your own charts, you need to make the necessary modifications so that MyChart points to the correct Chart object.

MyChart.ClearToMatchStyle

Adding A Shadow

One of the most interesting formatting effects in Excel 2007 is shadows. A shadow can give a chart a three–dimensional look and make it appear as if it's floating above your worksheet.

The following statement adds a default shadow to the chart area of the chart:

MyChart.ChartArea.Format.Shadow.Visible = msoTrue

In this statement, the Format property returns a ChartFormat object, and the Shadow property returns a ShadowFormat object. Therefore, this statement sets the Visible property of the ShadowFormat object, which is contained in the ChartFormat object, which is contained in the ChartArea object, which is contained in the Chart object.

Not surprisingly, the ShadowFormat object has some properties that determine the appearance of the shadow. Here's an example of setting five properties of the ShadowFormat object, contained in a ChartArea object, and Figure 15-3 shows the effect:

With MyChart.ChartArea.Format.Shadow
    .Visible = msoTrue
    .Blur = 10
    .Transparency = 0.4
    .OffsetX = 6
    .OffsetY = 6
End With

Figure 15-3. Applying a shadow to a chart.

The example that follows adds a subtle shadow to the plot area of the chart:

With MyChart.PlotArea.Format.Shadow
    .Visible = msoTrue
    .Blur = 3
    .Transparency = 0.6
    .OffsetX = 1
    .OffsetY = 1
End With

If an object has no fill, applying a shadow to the object has no visible effect. For example, a chart's title usually has a transparent background (no fill color). To apply a shadow to an object that has no fill, you must first add a fill color. This example applies a white fill to the chart's title and then adds a shadow:

MyChart.ChartTitle.Format.Fill.BackColor.RGB = RGB(255, 255, 255)
With MyChart.ChartTitle.Format.Shadow
    .Visible = msoTrue
    .Blur = 3
    .Transparency = 0.3
    .OffsetX = 2
    .OffsetY = 2
End With

Adding a Bevel

Adding a bevel to a chart can provide an interesting 3–D effect. Figure 15-4 shows a chart with a beveled chart area. To add the bevel, I used the ThreeD property to access the ThreeDFormat object. The code that added the bevel effect is as follows:

With MyChart.ChartArea.Format.ThreeD
    .Visible = msoTrue
    .BevelTopType = msoBevelDivot
    .BevelTopDepth = 12
    .BevelTopInset = 32
End With

Figure 15-4. This chart has a bevel effect.

Figure 15-5 shows an XY chart. The goal is to label each point with the corresponding name in column A. The following macro accomplishes this goal:

Sub ApplyDataLabels()
    Dim Ser As Series, Pt As Point
    Dim Counter As Long
    Set Ser = ActiveSheet.ChartObjects(1).Chart.SeriesCollection(1) Chapter 15: Advanced VBA Examples 469 Part III 21_044001 ch15.qxp 8/24/07 9:34 PM Page 469

Ser.HasDataLabels = True
    Counter = 1
    For Each Pt In Ser.Points
        Pt.DataLabel.Text = Range("A1").Offset(Counter, 0)
        Counter = Counter + 1
    Next Pt
End Sub

Figure 15-5. Excel does not provide a direct way to use the text in column A as data labels in the chart.

The ApplyDataLabels macro creates an object variable (Ser) that represents the chart's data series. It then sets the HasDataLabels property to True. (Without this statement, the macro would end with an error.) The next statement initializes a variable (Counter) to 1. The next four statements comprise a For Each–Next loop, which loops through each Point object in the series. The code sets the Text property of the Point object's DataLabel object equal to a cell that is offset from cell A1. The offset row is specified by the Counter variable, which is incremented each time through the loop.

Figure 15-6 shows the chart after executing the ApplyDataLabels macro.

A data label can contain a simple formula that refers to a cell. In such a case, the data label is linked to that cell ‐ if the cell changes, so does the corresponding data label. It's a simple matter to modify the ApplyDataLabels macro from the previous section so that it creates links to the cells. Only one statement needs to be changed:

Pt.DataLabel.Text = "=" & Range("A1").Offset(Counter, 0) _
  .Address(True, True, xlR1C1, True)

Figure 15-6. A macro adds data labels to the chart.

This statement inserts an equal sign (to indicate a formula), followed by the Address property of the cell. The Address property takes four arguments. After executing the modified macro, the first data label in the series contains this formula:

=Sheet1!$A$2

The preceding macros hard–code the data label range, so they are not general–purpose macros. A better approach is to specify the range interactively. The following macro uses the InputBox method to display a simple dialog box in which the user can specify a range by clicking a cell (see Figure 15-7).

Figure 15-7. The InputBox method prompts the user for a range.

The DataLabelsWithPrompt macro, which follows, is similar to the preceding macros but contains additional code to display the dialog box. The cell specified by the user is assigned to a Range object variable (RngLabels). Notice that a statement checks to see whether RngLabels is Nothing. That will be the case if the user clicks the Cancel button. If so, the macro ends with no action. The On Error Resume Next statement is present because clicking Cancel causes an error. That statement simply ignores the error. Also, notice the second Set statement that uses the RngLabels object. This statement ensures that if the user selects a multicell range, only the first cell in the selected range is assigned to RngLabels.

Sub DataLabelsWithPrompt()
    Dim RngLabels As Range
    Dim Ser As Series
    Dim Counter As Long
    On Error Resume Next


    If ActiveChart Is Nothing Then
        MsgBox "Select a chart."
        Exit Sub
    End If


    Set RngLabels = Application.InputBox _
        (prompt:="Range for data labels?", Type:=8)
    If RngLabels Is Nothing Then Exit Sub 'Canceled


    On Error GoTo 0
    If RngLabels.Rows.Count > 1 And RngLabels.Columns.Count > 1 Then
        MsgBox "Select a single row or column for labels."
        Exit Sub
    End If

    Set Ser = ActiveChart.SeriesCollection(1)
    Ser.HasDataLabels = True
    For Counter = 1 To Ser.Points.Count
        Ser.Points(Counter).DataLabel.Text = _
            RngLabels.Cells(Counter)
    Next Counter
End Sub

The DataLabelsWithPrompt procedure always works with the first series in the chart. A more general macro would prompt the user for the series if the chart has more than one series.

The examples in this section deal with the Worksheet_Change event, which is one of many events available at the worksheet level. This event is triggered whenever a cell is changed in a worksheet. As you'll see, these types of macros can be very useful when you want to update charts automatically.

Figure 15-11. Using the drop–down lists in a code module to select an event.

Hiding And Displaying Charts

The worksheet shown in Figure 15-12 contains five embedded charts, four of which are hidden. Cell B2 controls which of the five charts is visible. This cell uses data validation to display a list of values from 1 through 5, plus an additional entry: (none).

Figure 15-12. Cell B2 contains a drop–down list of chart numbers. The selected chart number is visible and the others are hidden.

The worksheet uses a Worksheet_Change event procedure, which follows. The macro is executed whenever a cell is changed on the sheet. Notice that this procedure has an argument: Target. This argument is a variable that represents the cell or range that is changed. If the address of Target is $B$2, the macro performs two operations: It hides all members of the ChartObjects collection and then unhides the chart that corresponds to the value in cell B2. The On Error statement ignores the error that occurs if an invalid ChartObject number is referenced, or when the user selects (none).

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$B$2" Then
        ActiveSheet.ChartObjects.Visible = False
        On Error Resume Next
        ChartObjects(Target.Value).Visible = True
    End If
End Sub

The five embedded charts are stacked on top of each other, so they all appear in the same position on–screen.

Adjusting Axis Scaling

The example in this section further uses an event macro to adjust a chart's value axis. Figure 15-13 shows the worksheet. The Axis property values are entered in the cells in column D. These cells are named AxisMin, AxisMax, and MajorUnit.

Figure 15-13. An event macro, executed when any cell on the sheet is changed, modifies properties for the chart's value axis.

The Worksheet_Change procedure that follows is executed whenever any cell in the sheet is changed. The first statement of the macro checks the Column property of the Target variable, which represents the cell that was changed. If the changed cell is in column D, the property values are retrieved from the sheet and applied to the chart. Otherwise, nothing happens.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 4 Then
        On Error Resume Next
        With ActiveSheet.ChartObjects(1).Chart.Axes(xlValue)
.MinimumScale = Range("AxisMin")
            .MaximumScale = Range("AxisMax")
            .MajorUnit = Range("MajorUnit")
       End With
   End If
End Sub

This procedure doesn't do any error handling. Rather, it uses an On Error Resume Next statement to ignore any errors that occur. For example, if the user types a non–numeric entry for the AxisMax setting, no error is generated.

A Selection_Change event occurs whenever the user selects a different cell or range. You can use this event to create interactive charts.

Changing The Beginning Point Of A Series

Figure 15-14 shows an example. The data displayed on the chart depends on the active cell. The chart displays 12 data points, beginning with the data in the row of the active cell.

Figure 15-14. This chart uses a Selection_Change event procedure to display 12 data points beginning in the row of the active cell.

The event macro, located in the code module for Sheet1, is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Ser As Series
    If ActiveCell.Column > 2 Or ActiveCell.Row < 3 Then Exit Sub

    Set Ser = ActiveSheet.ChartObjects("Chart 1").Chart.SeriesCollection(1)
'   Specify values
    Ser.Values = Range(Cells(ActiveCell.Row, 2), _
      Cells(ActiveCell.Row + 12, 2))
'   Specify category
      Ser.XValues = Range(Cells(ActiveCell.Row, 1), _
        Cells(ActiveCell.Row + 12, 1))
End Sub

Whenever the user makes a new cell or range selection, the Worksheet_SelectionChange macro is executed. This macro first checks the active cell. If the active cell is not within the two–column data range, the macro ends. Otherwise, it creates an object variable (Ser), which represents the chart's data series. It then sets the Values property and the XValues property of the Series object, based on the active cell.

Plotting Data In The Active Row

The example in this section is similar to the previous examples. It uses the Selection_Change event to display data that's determined by the active cell. Figure 15-15 shows a chart that displays the responses to a survey question. The data displayed in the chart depends on the position of the active cell.

The event procedure is as follows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Ser As Series
    If ActiveCell.Row > 2 And ActiveCell.Row < 17 Then
        ActiveSheet.ChartObjects("Chart 1").Visible = True
        Set Ser = ActiveSheet.ChartObjects("Chart 1"). _
          Chart.SeriesCollection(1)
        'Specify values

Ser.Values = Range(Cells(ActiveCell.Row, 2), _
        Cells(ActiveCell.Row, 6))
        'Specify title
        ActiveSheet.ChartObjects("Chart 1").Chart.ChartTitle.Text = _
           Cells(ActiveCell.Row, 1)
    Else
        ActiveSheet.ChartObjects("Chart 1").Visible = False
    End If
End Sub

This procedure uses an If–Then–Else construct. If the active cell is in a row that contains data, the code modifies the Values property of the Series object and also specifies the title. If the active cell is in any other row, the chart is hidden.

Figure 15-15. The chart displays the data in the row of the active cell.