Excel XY Chart Variations with VBA

This page builds on the example code in Quick Chart VBA Examples. This page contains sample VBA procedures for constructing XY charts using data arrangements which would strangle the Chart Wizard and lead to tedious redefinition of chart series data sources in the Source Data dialog or in the chart series formulas.

Outline: Excel XY Chart Variations with VBA

• Standard Series with Shared X Values and Distinct Y Values
  Default behavior: no code needed
• Series with Distinct X Values and Shared Y Values
  Includes sample procedure to build a chart
• Series with Distinct X and Y Values
  Includes sample procedure to build a chart
• Quick XY Chart Utility
  Constructs XY charts from several regular but nonstandard data arrangements

Excel XY Scatter Chart Variations

An XY Scatter chart with a single charted series has one set of X data and one set of Y data. When you select more than two columns (or rows) of data and choose a Scatter chart, Excel's standard treatment is to assume all series share the X values in the first column or row, and that each successive column or row holds the Y data for a separate series. Sometimes, your data may be structured differently. For example, all the series may share their Y values, but have distinct X values. Or more commonly, each series may have its own X values and Y values. Generally you would have to do a lot of cutting and pasting, or add all series after the first one, but you can write some relatively simple VBA precedures to construct these charts automatically.

Standard Series with Shared X Values and Distinct Y Values

When the Chart Wizard creates an XY Scatter chart from a worksheet range, such as the data range below left, Excel assumes the top row of the selected range contains series names, and the left hand column contains category (X) values. (In these tables, the X values are shown in aqua and the Y values in yellow.) The chart wizard creates a chart like the one below the table. The corresponding points on each series line up above each shared X value. No code is needed here, because Excel's default behavior treats the data just fine.

If you decide to make a line, column, or area chart from the table above, Excel will not always assume your chart should use the first column for its category values. If the first column contains data which is somehow recognizable as different from the rest of the columns (e.g., it is nonnumeric, such as text labels, or it consists of dates), Excel will decide to use it for categories. But if it is numeric, you will get a chart with one more series than expected. Notice that the line chart below has one lonely series at the bottom, labeled "X", whose Y values range from 1 to 11.

To help Excel parse the data range, no matter which chart type you will be using, simply clear the top left cell of the data range. This tells Excel that the top row is different from the other rows and the left column is different from the other columns. Another trick is to create an XY chart from the data, then change the entire chart to the type you want.

Top of Page

Series with Distinct X Values and Shared Y Values

Sometimes your data is differently arranged, and each series might have different X values for each shared Y value. The data and the chart below illustrate this.

Normally you'd have to do lots of cutting and pasting to chart these series of data. But the following simple VBA procedure will construct such a chart, as quickly as the Chart Wizard constructs a "regular" chart.

Option Explicit

Sub MultiX_OneY_Chart()

    Dim rngDataSource As Range
    Dim iDataRowsCt As Long
    Dim iDataColsCt As Integer
    Dim iSrsIx As Integer
    Dim chtChart As Chart
    Dim srsNew As Series

    If Not TypeName(Selection) = "Range" Then
        '' Doesn't work if no range is selected
        MsgBox "Please select a data range and try again.", _
            vbExclamation, "No Range Selected"
    Else
        Set rngDataSource = Selection
        With rngDataSource
            iDataRowsCt = .Rows.Count
            iDataColsCt = .Columns.Count
        End With

        '' Create the chart
        Set chtChart = ActiveSheet.ChartObjects.Add( _
            Left:=ActiveSheet.Columns(ActiveWindow.ScrollColumn).Left + _
                ActiveWindow.Width / 4, _
            Width:=ActiveWindow.Width / 2, _
            Top:=ActiveSheet.Rows(ActiveWindow.ScrollRow).Top + _
                ActiveWindow.Height / 4, _
            Height:=ActiveWindow.Height / 2).Chart

        With chtChart
            .ChartType = xlXYScatterLines

            '' Remove any series created with the chart
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop

            For iSrsIx = 1 To iDataColsCt - 1
                '' Add each series
                Set srsNew = .SeriesCollection.NewSeries
                With srsNew
                    .Name = rngDataSource.Cells(1, iSrsIx)
                    .Values = rngDataSource.Cells(2, iDataColsCt) _
                        .Resize(iDataRowsCt - 1, 1)
                    .XValues = rngDataSource.Cells(2, iSrsIx) _
                        .Resize(iDataRowsCt - 1, 1)
                End With
            Next
        End With
    End If
End Sub

Top of Page

Series with Distinct X and Y Values

Usually, each of the series that I end up charting have distinct X and Y values, as illustrated by the data and chart below.

As in the prior example, you'd have to do lots of cutting and pasting to chart these series manually. But the following simple VBA procedure will construct this chart, as easily as using the Chart Wizard.

Option Explicit

Sub MultiXY_Chart()

    Dim rngDataSource As Range
    Dim iDataRowsCt As Long
    Dim iDataColsCt As Integer
    Dim iSrsIx As Integer
    Dim chtChart As Chart
    Dim srsNew As Series

    If Not TypeName(Selection) = "Range" Then
        '' Doesn't work if no range is selected
        MsgBox "Please select a data range and try again.", _
            vbExclamation, "No Range Selected"
    Else
        Set rngDataSource = Selection
        With rngDataSource
            iDataRowsCt = .Rows.Count
            iDataColsCt = .Columns.Count
        End With
        If iDataColsCt Mod 2 > 0 Then
            MsgBox "Select a range with an EVEN number of columns.", _
                vbExclamation, "Select Even Number of Columns"
            Exit Sub
        End If

        '' Create the chart
        Set chtChart = ActiveSheet.ChartObjects.Add( _
            Left:=ActiveSheet.Columns(ActiveWindow.ScrollColumn).Left + _
                ActiveWindow.Width / 4, _
            Width:=ActiveWindow.Width / 2, _
            Top:=ActiveSheet.Rows(ActiveWindow.ScrollRow).Top + _
                ActiveWindow.Height / 4, _
            Height:=ActiveWindow.Height / 2).Chart

        With chtChart
            .ChartType = xlXYScatterLines

            '' Remove any series created with the chart
            Do Until .SeriesCollection.Count = 0
                .SeriesCollection(1).Delete
            Loop

            For iSrsIx = 1 To iDataColsCt - 1 Step 2
                '' Add each series
                Set srsNew = .SeriesCollection.NewSeries
                With srsNew
                    .Name = rngDataSource.Cells(1, iSrsIx + 1)
                    .Values = rngDataSource.Cells(2, iSrsIx + 1) _
                        .Resize(iDataRowsCt - 1, 1)
                    .XValues = rngDataSource.Cells(2, iSrsIx) _
                        .Resize(iDataRowsCt - 1, 1)
                End With
            Next
        End With
    End If
End Sub

Quick XY Chart Utility

After writing about procedures that can create charts from regular but nonstandard ranges, I built a utility that handles this very task. The utility creates XY charts exclusively, for ten different columnar data arrangements, including the three discussed above. Click on the dialog below to visit the Quick XY Chart Utility page.

Peltier Technical Services, Inc.

Excel Chart Software | Training | Consulting | Peltier Tech Blog

Peltier Technical Services, Inc., Copyright © 207. All rights reserved.
You may link to this article or portions of it on your site, but copying is prohibited without permission of Peltier Technical Services.

Microsoft Most Valuable Professional

My MVP Profile