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.AddActiveChart.ChartType = xlXYScatterLinesActiveChart.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 = xlXYScatterLinesEnd WithEnd 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 = xlXYScatterLinesEnd 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.NewSeriesActiveChart.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 WithEnd 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 WithEnd 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 SeriesSet MyNewSrs = ActiveChart.SeriesCollection.NewSeriesWith MyNewSrs.Name = "Fred".Values = "=Sheet1!Y_Range".XValues = Array(1, 2, 3)End WithEnd 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 ActiveChartDo Until .SeriesCollection.Count = 0.SeriesCollection(1).DeleteLoopEnd WithEnd 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 ChartObjectDim rngChtData As RangeDim rngChtXVal As RangeDim iColumn As Long' make sure a range is selectedIf TypeName(Selection) <> "Range" Then Exit Sub' define chart dataSet rngChtData = Selection' define chart's X valuesWith rngChtDataSet rngChtXVal = .Columns(1).Offset(1).Resize(.Rows.Count - 1)End With' add the chartSet myChtObj = ActiveSheet.ChartObjects.Add _(Left:=250, Width:=375, Top:=75, Height:=225)With myChtObj.Chart' make an XY chart.ChartType = xlXYScatterLines' remove extra seriesDo Until .SeriesCollection.Count = 0.SeriesCollection(1).DeleteLoop' add series from selected range, column by columnFor iColumn = 2 To rngChtData.Columns.CountWith .SeriesCollection.NewSeries.Values = rngChtXVal.Offset(, iCOlumn - 1).XValues = rngChtXVal.Name = rngChtData(1, iColumn)End WithNextEnd WithEnd 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 parentWith ActiveChart.Parent.Left = 100.Width = 375.Top = 75.Height = 225End WithEnd 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 ObjectDim rng As RangeSet cht = ActiveChart.ParentSet rng = ActiveSheet.Range("D5:K25")cht.Left = rng.Leftcht.Width = rng.Widthcht.Top = rng.Topcht.Height = rng.HeightEnd 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 LongDim nCharts As LongDim dTop As DoubleDim dLeft As DoubleDim dHeight As DoubleDim dWidth As DoubleDim nColumns As LongdTop = 75      ' top of first row of chartsdLeft = 100    ' left of first column of chartsdHeight = 225  ' height of all chartsdWidth = 375   ' width of all chartsnColumns = 3   ' number of columns of chartsnCharts = ActiveSheet.ChartObjects.CountFor iChart = 1 To nChartsWith ActiveSheet.ChartObjects(iChart).Height = dHeight.Width = dWidth.Top = dTop + Int((iChart - 1) / nColumns) * dHeight.Left = dLeft + ((iChart - 1) Mod nColumns) * dWidthEnd WithNextEnd 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