Creating Charts in Excel 2003 Using Visual Basic for Applications Code

 

Frank Rice
Microsoft Corporation

June 2004

Applies to:
    Microsoft® Office Excel 2003
    Microsoft Visual Studio® .NET

Summary: Charts provide a powerful way to visually represent series data that might otherwise be difficult to analyze. This article details methods of creating charts though the macro recorder, using Microsoft Visual Basic for Applications, and by using automation. (11 printed pages)

Contents

Introduction
Overview
Using Chart Sheets
Adding Chart to a Chart Sheet with the Macro Recorder
Adding a Chart Sheet with VBA Code
Using Embedded Charts
Adding an Embedded Chart with the Macro Recorder
Adding an Embedded Chart with VBA Code
Moving Charts between Chart Sheets and Worksheets
Using Automation to Create an Embedded Chart
Conclusion

Introduction

Microsoft® Office Excel 2003 includes a powerful and versatile charting engine. You can choose from a large variety of business and technical chart types. In addition, you can enhance the appearance of your charts with items such as lines, arrows, pictures, and so forth. In Excel, the chart represents one of the most widely used components for business applications. Essentially, a chart visually presents a table of numbers. Displaying data in a well thought out chart can make your data more understandable and can help you quickly get your point across during a presentation.

Because a chart is a visual representation of your data, it is particularly useful for understanding a relatively long series of numbers and their relationships. With a chart, you can identify trends and patterns that would be difficult to identify just in a series of numbers.

This article looks at:

  • Creating a chart object on separate sheets
  • Creating embedded chart objects in worksheets
  • Moving charts between worksheets and chart sheets
  • Creating a chart through automation

Overview

In Excel, you can create two types of charts; charts that occupy their own chart sheets, and charts that are embedded in a worksheet. The Chart object, which is a chart that exists as the only object in a chart sheet, is a member of the worksheet's Charts collection. The embedded chart, on the other hand, is contained in a worksheet that can contain other objects. The embedded chart is contained in the ChartObject object. The ChartObject is a member of the worksheet's ChartObjects collection and acts as a container for Chart objects. Properties and methods for the ChartObject object control the appearance and size of the embedded chart on the worksheet. The ChartObjects collection contains all the embedded charts on a single sheet.

Note   The ChartObject object is also a member of the Shapes collection. You can use the Shapes collection as an alternative way to refer to embedded charts on a worksheet.

Using Chart Sheets

The simplest way to create a chart in a chart sheet is to select your data and press F11. Your data is sent to whatever chart style is the default, placing the result on a new chart sheet. You can always modify the default chart type by clicking the Chart menu and then clicking Chart Type. Using techniques that are discussed later, you can also move the chart to a worksheet. The following sections describe other ways to create charts in chart sheets programmatically.

Adding Chart to a Chart Sheet with the Macro Recorder

Before creating the chart, start the macro recorder with the following steps:

  1. Start Excel.

  2. In the blank default worksheet, add the following data:

      A B C D
    1 Tools Sold Oct Nov Dec
    2 South 7000 6610 4827
    3 North 1155 2914 3790
    4 East 757 659 7072
    5 West 5012 3880 4752
  3. To start the macro recorder, on the Tools menu, point to Macro, and then click Record New Macro.

  4. In the Macro name box, type a name for the macro such as ChartMacro.

  5. In the Store macro in box, click the location where you want to store the macro.

    Note   If you want a macro to be available whenever you use Excel, select Personal Macro Workbook.

  6. Click OK.

Now create a chart based on the table of data as follows:

  1. Select the cells that contain the data you want to use for your chart. In this case A3:D7.

  2. On the Insert menu, click Chart to start the Chart Wizard.

  3. In Step 1 of the wizard, select the Column option and click Next.

  4. In Step 2, choose the Series in Rows option and click Next.

  5. In Step 3, type Tools Sales for Qtr 1 in the Chart Title box. Type Month in the Category (X) axis box and Sales in the Category (Y) axis box and click Next.

  6. In Step 4, choose the As new sheet option and type the name of the chart sheet as Tools Sold and then click Finish.

  7. To stop the macro recorder, on the Stop Recording toolbar, click Stop Recording.

  8. To review the recorded macro, on the Tools menu, click Macro, and then click Macros.

  9. In the Macro dialog box, click ChartMacro, and then click Edit. The recorded macro should look like the following:

    Range("A1:D5").Select
    Charts.Add
    ActiveChart.ChartType = xlColumnClustered
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:D7"), PlotBy:= _
        xlRows
    ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Tools Sold"
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "Tools Sales for Qtr 1"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
    End With
    

Looking at the code, the macro uses the Add method of the Charts collection to create a chart. Then the active chart's ChartType property is defined, and then uses the SetSourceData method to define the ranges plotted. The macro uses the Location method to define the chart as a chart sheet and assign it a name. It sets the HasTitle property to True so that it can define the ChartTitle property. Next, it sets the HasTitle property of the axes to True and then assigns the title of the two axes. And finally, Sheet1 is selected.

Adding a Chart Sheet with VBA Code

The code created by the macro recorder is somewhat verbose and can contain some redundant code. Using Microsoft Visual Basic® for Applications (VBA) provides a simpler and efficient way of referring to the chart code. In addition, using VBA code allows you to add a chart title that is linked to the worksheet. Type the following code into the Visual Basic Editor and run it:

Sub AddChartSheet()
   Dim chtChart As Chart
   'Create a new chart.
   Set chtChart = Charts.Add
   With chtChart
      .Name = "Tool Sales2"
      .ChartType = xlColumnClustered
      'Link to the source data range.
      .SetSourceData Source:=Sheets("Sheet1").Range("A1:D5"), _
         PlotBy:=xlRows
      .HasTitle = True
      .ChartTitle.Text = "=Sheet1!R1C2"
      .Axes(xlCategory, xlPrimary).HasTitle = True
      .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
      .Axes(xlValue, xlPrimary).HasTitle = True
      .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
   End With
End Sub

In this code, the Location method is removed since it isn't required. A chart sheet is created by default. Notice that the chart title has been defined as a formula. The Chart Wizard, on the other hand, doesn't allow you to enter a formula. The remainder of the code is just like that created by the macro recorder.

Note   Data entered as a formula in the chart Text property must be defined as the R1C1 address method and not as the A1 address method.

Using Embedded Charts

A chart embedded on a worksheet is an object, just like an embedded graphic, control, or anything else added to your worksheet.

Note   One thing to remember about working with an embedded chart object is to select it as an object. To do this you need to hold down the CTRL key while clicking the chart. Otherwise, you are selecting an element of the chart instead of the entire object. To verify this, white handles appear around the perimeter of the chart and the name of the chart is in the name box. Black handles around the chart indicate that you have selected an element of the chart.

Adding an Embedded Chart with the Macro Recorder

  1. Start Excel.

  2. Click Sheet1 in the default workbook (or the worksheet that contains your data).

  3. Start the macro recorder by clicking the Tools menu, pointing to Macro, and then clicking Record New Macro.

  4. In the Macro name box, type a name for the macro such as EmbeddedChart.

  5. In the Store macro in box, click the location where you want to store the macro.

    Note   If you want a macro to be available whenever you use Excel, select Personal Macro Workbook.

  6. Click OK.

Now create a chart based on the table of data as follows:

  1. Select the cells that contain the data you want to use for your chart. In this case A1:D5.

  2. On the Insert menu, click Chart to start the Chart Wizard.

  3. In Step 1 of the wizard, select the Column option and click Next.

  4. In Step 2, choose the Series in Rows option and click Next.

  5. In Step 3, type Tools Sales for Qtr 1 in the Chart Title box. Type Month in the Category (X) axis box and Sales in the Category (Y) axis box and click Next.

  6. In Step 4, choose the As object in option and type the name of the chart sheet (such as Sheet1) and then click Finish.

  7. To stop the macro recorder, on the Stop Recording toolbar, click Stop Recording.

  8. To review the recorded macro, on the Tools menu, click Macro, and then click Macros.

  9. In the Macro dialog box, click EmbeddedChart (or the name of your macro), and then click Edit. The recorded macro should look similar to the following:

    Note   This code relies on the data created in the section of this article titled Adding Chart to a Chart Sheet with the Macro Recorder.

        Range("A1:D5").Select
        Charts.Add
        ActiveChart.ChartType = xlColumnClustered
        'Link to the source data range.
        ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:D5"), PlotBy:= _
            xlRows
        ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
        With ActiveChart
            .HasTitle = True
            .ChartTitle.Characters.Text = "Tools Sales for Qtr 1"
            .Axes(xlCategory, xlPrimary).HasTitle = True
            .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "Month"
            .Axes(xlValue, xlPrimary).HasTitle = True
            .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "Sales"
        End With
    

This recorded macro code is similar to that created for the chart added to the chart sheet including the definition of the chart. The exception is that it uses the Location method to define the chart as embedded.

Adding an Embedded Chart with VBA Code

The following VBA subroutine uses the Parent property of the embedded chart to identify the ChartObject object:

Note   This code relies on the data created in the section of this article titled Adding Chart to a Chart Sheet with the Macro Recorder.

Sub AddChart()
   Dim chtChart As Chart
   
   ActiveSheet.ChartObjects.Delete
   'Create a new chart.
   Set chtChart = Charts.Add
   Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")
   With chtChart
      .ChartType = xlColumnClustered
      'Set data source range.
      .SetSourceData Source:=Sheets("Sheet1").Range("A1:D5"), PlotBy:= _
         xlRows
      .HasTitle = True
      .ChartTitle.Text = "=Sheet1!R1C1"
      'The Parent property is used to set properties of 
      'the Chart.
      With .Parent
        .Top = Range("F9").Top
        .Left = Range("F9").Left
        .Name = "ToolsChart2"
      End With
   End With
End Sub

The first thing that happens in this procedure is that any existing ChartObjects objects are deleted. Next the chtChart object variable is set to refer to the added chart. By default, the new chart is located on a chart sheet, so the Location method is used to define the chart as an embedded chart. Then the chart type, source data, and chart title are defined. Notice the use of the Parent property. The ChartObject object is a container for the Chart object. The ChartObject object has properties and methods for controlling the appearance and size of the embedded chart on the worksheet. The Parent property is used to set such properties as the Top and Left of the ChartObject object which affect the Chart object.

Moving Charts between Chart Sheets and Worksheets

As stated earlier, an Excel chart can appear embedded in a worksheet or reside in a separate chart sheet. We demonstrated two ways to store charts on a chart sheet. The following procedure demonstrates how to store multiple charts on a single chart sheet. Use this routine to create the embedded charts, placing them in a worksheet.

Note   Putting multiple charts on a single chart sheet lets you use the View menu, Sized with Window option (available when the chart sheet is active) to scale the charts to the window size and dimensions.

This code relies on the data created in the section of this article titled Adding Chart to a Chart Sheet with the Macro Recorder. Also, this is the same code as the previous AddChart() routine with the Delete method removed.

  1. Create the first embedded chart.

    Sub AddChart()
       Dim chtChart As Chart
       'Create a new chart.
       Set chtChart = Charts.Add
       Set chtChart = chtChart.Location(Where:=xlLocationAsObject, Name:="Sheet1")
       With chtChart
        .ChartType = xlColumnClustered
        'Set the source data range for the chart.
        .SetSourceData Source:=Sheets("Sheet1").Range("A1:D5"), PlotBy:= _
            xlRows
        .HasTitle = True
        .ChartTitle.Text = "=Sheet1!R1C1"
        With .Parent
          .Top = Range("F3").Top
          .Left = Range("F3").Left
          .Name = "ToolsChart1"
        End With
      End With
    End Sub
    
  2. Change the coordinates of the top and left location of the chart and the name of the chart with the existing structure to create the second embedded chart:

    ....
        With .Parent
          .Top = Range("F19").Top
          .Left = Range("F19").Left
          .Name = "ToolsChart2"
        End With
    ....
    
  3. Select any blank cell the worksheet and press F11; this creates an empty chart sheet to hold the embedded charts.

  4. Reactivate your worksheet, click an embedded chart, and click the Chart menu and then click Location to display the Chart Location dialog box.

  5. Choose As object in and specify the empty chart sheet and click OK. Excel will transfer the embedded chart to your chart sheet.

  6. Select your remaining chart, and then click the Chart menu and Location to move it to the chart sheet using steps 4 and 5. Now you can arrange and size the charts any way you like.

  7. To move a chart from a chart sheet to a worksheet, click the chart in the chart sheet to activate it, click the Chart menu, and then click Location.

  8. Next, click the As object in option, select the worksheet from the drop-down list, and click OK. The chart is moved to the specified worksheet and deleted from the chart sheet.

Using Automation to Create an Embedded Chart

This section illustrates the code that uses automation in a Microsoft Visual Basic® program to create an Excel workbook that contains a chart embedded on a worksheet using the following steps:

  1. Start Microsoft Visual Studio .NET. On the File menu, click New, and then click Project. Select Windows Application from the Visual Basic Projects types. Form1 is created by default.

  2. Add a reference to the Excel object library. To do this, follow these steps:

    1. On the Project menu, click Add Reference.
    2. On the COM tab, locate Microsoft Excel 11.0 Object Library, and then click Select.
    3. Click OK in the Add References dialog box to accept your selections. If you receive a prompt to generate wrappers for the libraries that you selected, click Yes.
  3. On the View menu, click ToolBox. Add a combo box and a button to Form1.

  4. Double-click Button1 to generate a definition for the button's Click event handler.

  5. Add the following code to the Button1_Click event procedure:

        Dim objXL As Object
        Dim objBook As Object
        Dim objSheet As Object
        Dim objChart As Object
    
        Dim iRow As Integer
        Dim iCol As Integer
    
        ' Number of points in each Series
        Const cNumCols = 10
        'Number of Series
        Const cNumRows = 2
        'Declare the required size of the dynamic array
        Dim aTemp(cNumRows, cNumCols)
    
        'Start Excel and create a new workbook
        Set objXL = CreateObject("Excel.application")
        Set objBook = objXL.Workbooks.Add
        'Set a reference to Sheet1
        Set objSheet = objBook.Worksheets.Item(1)
    
        ' Insert Random data into Cells for the two Series:
        Randomize(Now().ToOADate())
        For iRow = 1 To cNumRows
           For iCol = 1 To cNumCols
              aTemp(iRow, iCol) = Int(Rnd * 50) + 1
           Next iCol
        Next iRow
        objSheet.Range("A1").Resize(cNumRows, cNumCols).Value = aTemp
    
        ' Add a chart object to the first worksheet
        Set objChart = objSheet.ChartObjects.Add(50, 40, 300, 200).Chart
        objchart.SetSourceData(Source:=objSheet.Range("A1").Resize(cNumRows, cNumCols))
        ' Make Excel Visible:
        objXL.Visible = True
    
        objXL.UserControl = True
    
  6. Press the F5 key to run the program, and click the command button. Excel starts, and a new workbook is created. Random data is added to cells A1:J2 and a chart is embedded on the first worksheet. The embedded chart uses the random data for its source.

After creating a new Excel application and workbook, this procedure uses the Randomize function to initialize the random-number generator and to return the random numbers used to populate the chart. Next, the columns and rows starting at A1 are filled with the values from the array. Then the chart is added to worksheet1 and the position and size is defined. And finally, the Resize method is called to allow the range starting at cell A1 to fill the screen and assigning those values as the data source.

Conclusion

Charts provide a powerful way to visually represent series data that might otherwise be difficult to analyze. There are two methods of representing charts on worksheets; as an embedded object and in its own chart sheet. You can create both of these types using either the VBA created by the macro recorder or through VBA you enter directly in the Visual Basic Editor. You can also create charts in Excel using automation. This article detailed ways of creating both types using both methods. In addition, creating a chart from Visual Basic is also illustrated. These techniques give you flexibility in how and the type you use for you applications.