Books at Amazon.com
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
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).
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.
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
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.
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.
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.
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
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
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:
Peltier Technical Services, Inc., Copyright © 2014. All rights reserved.