Update Graph

You can update the graph using Visual Basic code. In this section, you will create a chart that you can update by adding points to the end of the data. We will also allow room at the top for descriptions and use data where the first column is x-axis descriptors and the second column is the y-axis data.

To update the data in a graph, use named ranges. To name a range, use the Name property of the Workbook. For example, name the range for cell E6 MyCell.

Define Name dialog box

    Names.Add Name:="MyCell", RefersTo:=Cells(6, 5)

You can check this on the worksheet. Click on cell E6. You should see the name MyCell to the left in the name box of the formula toolbar. You can also click the menu Insert > Name > Define... . You will see the name and the corresponding cell location in the dialog.

You can print all the names and definitions on the Excel spreadsheet. Go to the menu Insert > Name > Paste... and click Paste List. This will list the names starting at the active cell in the worksheet.

Once you have a name for the cell, you can put a formula in the cell such as counting the number of data points in a range. The cell then acts as variable. The cell containing this formula will tell us how many data points we have. Since the range for the chart data can get complicated, assign it a name also. The following is code for counting the number of data points. StrTemp is a string that will look like = CountA($A$2:$A$1000). If rng represents the cell A1, it will count the number of points in the first column starting at A2 up to A1000.

    Dim strTemp As String
    
    strTemp = "= CountA(" & Range(rng.Cells(2, 1), rng.Cells(1000, 1)).Address & ")"
    rng.Cells(1, 5) = strTemp
    rng.Parent.Names.Add Name:="NumberPoints", RefersTo:=rng.Cells(1, 5)

Now make the x-axis formula for the chart using the name NumberPoints. NumberPoints will act as a variable for our formula. This gives us the ability to have a changing range for the x-axis. StrFirstDataCell is a string that will give the explicit address for the upper left cell that works in the OFFSET function. When the upper left cell is A!, the formula for strFirstDataCell will look like [graphExample.xls]Sheet1!R1C1.

Next, build the x-axis. XaxisFormula is a string that is used as the range for the x-axis in the chart. It is assigned the name Xaxis which is also a string. With the upper left cell as A1, the formula for XaxisFormula will look like OFFSET([graphExample.xls]Sheet1!R1C1,1,0,NumberPoints,1).

    strFirstDataCell = rng.Cells(1, 1).Address(ReferenceStyle:=xlR1C1, external:=True)
    XaxisFormula = "OFFSET(" & strFirstDataCell & ",1,0," & "NumberPoints" & ",1)"
    Names.Add Name:= "Xaxis", RefersToR1C1:="=" & XaxisFormula

Reference the x-axis for the y-axis. The y-axis is the x-axis, but one column to the right.

YaxisFormula = "OFFSET(" & "Xaxis" & ",0,1,,)" Names.Add Name:= "Yaxis", RefersToR1C1:="=" & YaxisFormula

In the code example, the strings such as XaxisFormula are string variables that will be used as a named formula. The final naming and formulas can be copied to the worksheet for viewing after running the code. This is an excellent way to debug the these strings since they can get more complicated. You can view the formulas in the Excel worksheet with the menu Insert > Name > Paste...

All this was done to create an x-axis name and y-axis name that is a range that will vary in column height depending on the amount of data in the x-axis column. The first cell we created, NumberPoints, counts the number of points in the x-axis column. Use this cell to access the changing data.

Before creating the chart, make dummy data on the worksheet. The dummy data must consist of two strings or axis titles in the top row, one for each cell. Put titles "X-axis" and "Y-axis" in those two cells. Below these titles, the chart expects strings in the second cell of the first column. Put "xxx" there. This will be overwritten with x-axis data. The chart expects a number in the second cell of the second column. Put a "1" there.

    With rng
        .Cells(1, 1) = "X-axis"
        .Cells(1, 2) = "Y-axis"
        .Cells(2, 1) = "xxx"
        .Cells(2, 2) = 1
    End With

Now, the named ranges are valid and you can create the chart. Set the chart Source to the dummy data in the top two cells and one row of data below that, enclosing a 2 x 2 area. That's just enough data to allow creation of the chart. The following code will create the chart.

    Dim SheetName As String
    Dim chrt As Chart
    Dim sourceAddress As String
    
    SheetName = rng.Parent.Name
    Set chrt = Charts.Add
    sourceAddress = Range(rng.Cells(1, 1), rng.Cells(2, 2)).Address()
    chrt.SetSourceData Source:=Sheets(SheetName).Range(sourceAddress), PlotBy _ 
        :=xlColumns

At this point the chart has figured out that the first column should be the x-axis, that the second column should be y-axis, that there are titles (the top of the y-axis is a string) and that the x-axis data is annotation for the x-axis (because the second cell is a string). The chart thinks there is only one data point. You have to fix that. The chart must expect that the x-axis/y-axis data is a named range. Change the chart like this.

    With chrt.SeriesCollection(1)
        ' This does not work when sheetname has a space character
        .XValues = "=" & SheetName & "!" & nameXaxis
        .Values = "=" & SheetName & "!" & nameYaxis
    End With

Once this naming takes place, you can change the data size and the chart will update for you. Try the sample macro "MakeChart" in the Excel Examples. Once the chart and data are in the worksheet, erase the bottom half of the data. The data on the chart updates. Try clicking on the data points on the chart. The data range will have a halo around it indicating the data on the chart. Right-click on a blank area of the chart. Select Source Data... and then the Series tab. Look at the ranges for the x and y-axis. If everything is working, they will be the named ranges.

You can open the Excel example spreadsheet graphexample.xls with the following link. graphexample.xls

Related Links