Excel Chart Add-Ins | Training | Charts and Tutorials | Peltier Tech Blog

Quick Excel Chart VBA Examples

The use of VBA in Microsoft Excel charting is a topic that would fill a large volume. The examples here are straightforward illustrations of easy techniques to create charts, add and remove series, and move and resize charts using VBA.

Outline: Quick Chart VBA Examples

VBA Code to Add Chart Objects and Series

Add a Chart
Add a Series
Too Many Series?
Putting It All Together
Further examples: Excel XY Chart Variations with VBA

VBA Code to Resize and Reposition Chart Objects

Chart Object Size and Position
Cover a Range with a Chart
Create an Array of Charts

Error Free VBA
Basic techniques to reduce errors in the use of your code

VBA Code to Add Chart Objects and Series

Add a Chart

When you record a macro to add a chart object to a worksheet, Excel comes up with the following code:

Sub RecordedAddChartObject()
'
' RecordedAddChartObject Macro
' Macro recorded 5/2/02 by Jon Peltier
'
    Charts.Add
    ActiveChart.ChartType = xlXYScatterLines
    ActiveChart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
    ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet1"
End Sub

Excel uses Charts.Add followed later by ActiveChart.Location to create a chart object, and uses ActiveChart.SetSourceData to set all the series data in one shot. The coding is efficient in terms of the small length of the code, but inflexxible in terms of your control over the output.

In my examples I use ChartObjects.Add, which also requires (or allows) me to state the position and size of the chart. This example does almost exactly what the recorded macro above does:

Sub AddChartObject()
'
    With ActiveSheet.ChartObjects.Add _
            (Left:=100, Width:=375, Top:=75, Height:=225)
        .Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
        .Chart.ChartType = xlXYScatterLines
    End With
End Sub

The difference in the charts produced by the recorded macro and by the amended code are slight. The amended code positions and sizes the chart according to (Left:=100, Width:=375, Top:=75, Height:=225), where these dimensions are in pixels. The Chart Wizard creates a chart roughly half as wide and half as tall as the visible part of the worksheet window, centered within the window (if you have frozen panes in the sheet, the chart is half the size of the active pane, subject to certain minimum dimensions).

Errors in recorded macros

There are some instances when a recorded macro will contain code that will not work properly. For example, a recorded macro always puts ActiveChart.ChartType ahead of ActiveChart.SetSourceData, but I have reversed them in the code above. The steps given by the recorder work fine for most chart types, but a few chart types cannot be correctly assigned until the chart has been populated with sufficient data. In particular, code which creates stock charts and bubble charts will fail if you do not reverse the order of the steps.

When working with a surface or contour chart, Excel will let you manually delete a series in the chart (via the Source Data dialog, because you cannot select a single series in such a chart). You can record a macro to see the steps you took. When you try to run the macro, however, it will crash on the .Delete line in the macro. In most chart types the series can be independently formatted, and they don't even have to be the same type, leading to the ability to create custom combination charts. The problem with surface or contour charts is that their series are not treated by VBA as independent series. The trick in this case is to temporarily convert the surface chart to another chart type, say, a line chart, delete the series, then convert back to a surface chart.

In what I think of as "Marker charts", that is, XY and Line charts, VBA cannot access certain series properties, including .Values, .XValues, and .Formula if the source range of the series contains no chartable data (i.e., it consists of blanks or errors). This severe inconvenience can be avoided if you change the series type to an area or column chart type before accessing the forbidden properties, then change it back to a Line or XY chart series.

Inefficiencies in recorded macros

Aside from error-raising macro problems, a recorded macro is less efficient, because it mimics all the mouseclicks and keystrokes (every cough and camera flash) that occurred while the recording was taking place. A recorded macro clicks on every object to select it, then performs an action on the selection:

    ActiveChart.Axes(xlValue).Select
    Selection.TickLabels.Font.Bold = True
    Selection.TickLabels.NumberFormat = "0.0"

Streamline your code by replacing all the Object.Select plus Select.Property sequences with shorter Object.Property statements:

    ActiveChart.Axes(xlValue).TickLabels.Font.Bold = True
    ActiveChart.Axes(xlValue).TickLabels.NumberFormat = "0.0"

If you have two or more property or method statements that work on the same object, wrap them in a With/End With block:

    With ActiveChart.Axes(xlValue).TickLabels
        .Font.Bold = True
        .NumberFormat = "0.0"
    End With

There are other inefficiencies specific to charting, such as the Charts.Add . . . ActiveChart.Location sequence which can be shortened to a single ChartObjects.Add command, as discussed earlier in this page.

You may find it more convenient, in a longer procedure, to define some object variables. The next procedure does the same as the two above, but it uses a ChartObject variable for the new chart object that we create. If we need to refer to this chart object later in the procedure, we can conveniently use the variable myChtObj.

Sub AddChartObject()
Dim myChtObj As ChartObject
'
    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=375, Top:=75, Height:=225)
    myChtObj.Chart.SetSourceData Source:=Sheets("Sheet1").Range("A3:G14")
    myChtObj.Chart.ChartType = xlXYScatterLines
End Sub

You are not limited to using SetSourceData to define the data being charted. You can add the series one-by-one, selecting the precise data you want, not what Excel will assume you want.

Top of Page

Add a Series

The following is a macro I recorded while adding a series using the Add command in the Source Data dialog:

Sub RecordedAddSeries()
'
' RecordedAddSeries Macro
' Macro recorded 5/2/02 by Jon Peltier
'
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(6).XValues = "=Sheet1!R4C1:R14C1"
    ActiveChart.SeriesCollection(6).Values = "=Sheet1!R4C7:R14C7"
    ActiveChart.SeriesCollection(6).Name = "=Sheet1!R3C7"
End Sub

This is basically my preferred syntax, although the following has been neatened up in a few ways. First, the index of the series is not mentioned in the code, so it's more readily reused. Second, I can use the familiar A1 cell address notation, or any VBA range reference technique.

Sub AddNewSeries()
    With ActiveChart.SeriesCollection.NewSeries
        .Name = ActiveSheet.Range("G3")
        .Values = ActiveSheet.Range("G4:G14")
        .XValues = ActiveSheet.Range("A4:A14")
    End With
End Sub

And as shown below, this is very flexible. For example, you can adjust the code to insert a string for the series name, a VBA array for X or Y values (X values in the following code), or a reference to a defined range name (Y_Range for Y values).

Sub AddNewSeries()
    With ActiveChart.SeriesCollection.NewSeries
        .Name = "Fred"
        .Values = "=Sheet1!Y_Range"
        .XValues = Array(1, 2, 3)
    End With
End Sub

As with the chart object variable above, you can define an object variable for the new chart series being added. The following procedure assigns the variable MyNewSrs to the new chart series it creates.

Sub AddNewSeries()
    Dim MyNewSrs As Series
    Set MyNewSrs = ActiveChart.SeriesCollection.NewSeries
    With MyNewSrs
        .Name = "Fred"
        .Values = "=Sheet1!Y_Range"
        .XValues = Array(1, 2, 3)
    End With
End Sub

Top of Page

Too Many Series?

When you create a chart, Excel looks at the selection, and tries to determine how many series you want in the chart. In the Chart Wizard, you see this behavior in step 2, where the Data Range is tentatively filled in for you. When creating a chart in code, you don't get this chance to make it right, and your chart may have any number of series. It is best to clear out all of these initial series, and start from scratch with the series you intend to add. This macro clears the chart:

Sub RemoveUnwantedSeries()
    With ActiveChart
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop
    End With
End Sub

The chart now appears completely blank, and the only object you can currently access is the chart area. But you can now add series as shown above.

Top of Page

Putting It All Together

Let's put the last few steps together, to produce a robust little procedure that will create a chart sheet with exactly the right number of series in the right place, using the selected range as the chart's data source. The first row contains the series labels, the first column contains the X values, and the rest of the columns contain the Y values for each series.

Sub EmbeddedChartFromScratch()
    Dim myChtObj As ChartObject
    Dim rngChtData As Range
    Dim rngChtXVal As Range
    Dim iColumn As Long

    ' make sure a range is selected
    If TypeName(Selection) <> "Range" Then Exit Sub

    ' define chart data
    Set rngChtData = Selection

    ' define chart's X values
    With rngChtData
        Set rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)
    End With

    ' add the chart
    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=250, Width:=375, Top:=75, Height:=225)
    With myChtObj.Chart

        ' make an XY chart
        .ChartType = xlXYScatterLines

        ' remove extra series
        Do Until .SeriesCollection.Count = 0
            .SeriesCollection(1).Delete
        Loop

        ' add series from selected range, column by column
        For iColumn = 2 To rngChtData.Columns.Count
            With .SeriesCollection.NewSeries
                .Values = rngChtXVal.Offset(, iCOlumn - 1)
                .XValues = rngChtXVal
                .Name = rngChtData(1, iColumn)
            End With
        Next

    End With

End Sub

A similar approach is used in Interactive Chart Creation, which provides dialogs for the user to select the range which will be covered by the chart and the range containing the data to be charted. The series-by-series definition of X and Y ranges is described in Excel XY Chart Variations with VBA to allow much more flexibility in the arrangement of the chart's source data range. The Quick Chart Utility is based on this approach.

Top of Page

VBA Code to Resize and Reposition Chart Objects

Chart Object Size and Position

We learned above that it is easy to define the size and position of a chart at the time of its creation:

    Set myChtObj = ActiveSheet.ChartObjects.Add _
        (Left:=100, Width:=375, Top:=75, Height:=225)

In the same way, we can position or size the chart, by changing the appropriate properties of the ChartObject:

Sub ResizeAndRepositionChart()
    ' The ChartObject is the Chart's parent
    With ActiveChart.Parent
        .Left = 100
        .Width = 375
        .Top = 75
        .Height = 225
    End With
End Sub

To adjust a particular chart on the sheet, use With ActiveSheet.ChartObjects(1) in place of With ActiveChart.Parent in the procedure above.

Top of Page

Cover a Range with a Chart

You can easily configure the chart to cover a specific range of cells on the worksheet. To cover the range D5:K25 with the active chart, run this procedure:

Sub CoverRangeWithChart()
    Dim cht As Chart Object
    Dim rng As Range

    Set cht = ActiveChart.Parent
    Set rng = ActiveSheet.Range("D5:K25")

    cht.Left = rng.Left
    cht.Width = rng.Width
    cht.Top = rng.Top
    cht.Height = rng.Height
End Sub

Top of Page

Create an Array of Charts

Suppose you have a lot of charts on a worksheet, and you'd like to arrange them neatly. The following procedure loops through the charts, resizes them to consistent dimensions, and arranges them in systematic rows and columns:

Sub ArrangeMyCharts()
    Dim iChart As Long
    Dim nCharts As Long
    Dim dTop As Double
    Dim dLeft As Double
    Dim dHeight As Double
    Dim dWidth As Double
    Dim nColumns As Long

    dTop = 75      ' top of first row of charts
    dLeft = 100    ' left of first column of charts
    dHeight = 225  ' height of all charts
    dWidth = 375   ' width of all charts
    nColumns = 3   ' number of columns of charts
    nCharts = ActiveSheet.ChartObjects.Count

    For iChart = 1 To nCharts
        With ActiveSheet.ChartObjects(iChart)
            .Height = dHeight
            .Width = dWidth
            .Top = dTop + Int((iChart - 1) / nColumns) * dHeight
            .Left = dLeft + ((iChart - 1) Mod nColumns) * dWidth
        End With
    Next
End Sub

Top of Page

Error Free VBA

Although you cannot make your VBA code error-free, you can at least try to make it error-resistant. In several pages on this site, I've presented some VBA procedures to help create and modify your charts. Here I outline a few basic techniques to reduce the effects of errors in the use of your code. Replace an inscrutable VBA error message with a more descriptive message of your own:

Top of Page

Peltier Technical Services, Inc.

Excel Chart Add-Ins | Training | Charts and Tutorials | PTS Blog

Peltier Technical Services, Inc., Copyright © 2017. 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