Creating Charts with VB in Excel

You can use Excel to display data graphically and update when new data is available. All the graphs in this section are simple line charts. This means we plot only y-values and increment the x-axis by a constant.

Creating a graph in Excel is a matter of inserting data into a worksheet, setting the range and adding a chart. If you are not familiar with ranges in Excel, refer to the Excel documentation to learn how to use this powerful object. Ranges are essential for working with arrays. For our purposes, a range in Excel allows us to name one or a group of cells. The group of cells can be a variable. Using the name of a range will allow you to keep adding data to a graph as it becomes available. This section will show you how to modify the range definition such that additional data can be added.

To put a number on a worksheet you can use the Cells object or the range object. All of the following equations will put the value 3 in the same location, cell E3.

    Cells(3, 5) = 3
    Cells(3, 5).Value = 3
    Range("E3").Value = 3
    Range("E2").Offset(1, 0) = 3

Notice how Offset moves the location from E2 to E3.  Note that the arguments in parenthesis for the Cells and the Offset command are (row, column).

Array

You can insert a one-dimensional array as a row in the worksheet using the range object. In the next example we put data in a single dimension array. To insert the data you have to create a range by specifying the upper left cell and the lower right cell. To create the lower right cell you can use Offset referenced to the upper left cell. The row offset is zero to give the same row as the UpperLeft cell. The column is set to the number of data points minus one.

Once the range is described, you set the range value equal to the data. ActiveCell.Address returns a string such as "A1". This code will create a one-dimensional array of data, and insert it in the worksheet with the upper left cell the active cell. The following code will create a row of data.

    Dim data() As Double
    Dim i As Long
    Dim rng As Range
    Dim UpperLeftCell As String
    Dim numbPoints As Long
 
    UpperLeftCell = ActiveCell.Address
 
    numbPoints = 20
    ReDim data(numbPoints - 1)
 
    For i = 0 To numbPoints - 1
        data(i) = i + 1
    Next i
 
    Set rng = Range(Range(UpperLeftCell), Range(UpperLeftCell).Offset(0, numbPoints _       
        - 1))
    rng.Value = data

To insert the data as a column, you need a two-dimensional array. The routine for a column of data is below. Declarations and such not shown are the same as those shown above.

    ReDim data(numbPoints - 1, 0)
 
    For i = 0 To numbPoints - 1
        data(i, 0) = i + 1
    Next i
 
    Set rng = Range(Range(UpperLeftCell), Range(UpperLeftCell).Offset(numbPoints - 1,_
        0))
    rng.Value = data

Simple Graph

Now that we can put an array into a worksheet, let's see how to create a graph.  One of the best ways to learn the Excel commands is to use the macro recorder.  Select worksheet cells A1 to A20.  Turn on the macro recording by going to the menu Tools > Macro > Record New Macro.  Using the Chart Wizard, make a line chart from the data.  View the macro by pressing Alt+F8 and selecting the macro from the list.  Click Edit.

    Charts.Add
    ActiveChart.ChartType = xlLineMarkers
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A1:A20")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"

For more flexibility, set the Source of the graph to a variable rather than a string. Use the range of the data on the worksheet. This allows you to change the graph data in code. In the code below, on the right hand side of the equation, the term Range(upperLeftCell) represents the upper left cell, and the term Range(upperLeftCell).Offset(numbPoints, 0) represents the lower right cell of the outer range.

    Set dataRange = Range(Range(upperLeftCell), Range(upperLeftCell)._
        Offset(numbPoints, 0))
    ActiveChart.SetSourceData Source:= dataRange, PlotBy:=xlColumns

Setting the graph source this way allows you to create a graph with a variable number of points for a more general program. This code will place an array of data in the worksheet and then create a graph regardless of the size of the data or where the data is placed.

    upperLeftCell = "A1"
    numbPoints = 50

    ' put the data on the active sheet (one column)
    Set dataRange = Range(Range(upperLeftCell), Range(upperLeftCell)._
        Offset(numbPoints-1, 0))
    dataRange.Value = data
 
    SheetName = ActiveSheet.Name
 
    Charts.Add
    With ActiveChart
        .HasTitle = True
        .ChartTitle.Characters.Text = "My Chart"
        .ChartType = xlLineMarkers
        .SetSourceData Source:=DataRange, PlotBy:=xlColumns
        .Location Where:=xlLocationAsObject, Name:=SheetName
    End With