Creating Charts in a Grid

March 2008
S	M	T	W	T	F	S
« Feb		Apr »
	1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30	31

by Jon Peltier
Peltier Technical Services, Inc., Copyright © 2008. All rights reserved.

A few days ago, in Arranging Charts in a Grid, I posted a VBA procedure that arranged all the chart objects on a worksheet into a regular grid. This is pretty nice, but it arranges the charts left to right, then top to bottom, based on the Z stacking order of the charts. If you make the charts in a different order, or change their stacking order, you will get a different positioning of the various charts within the grid.

Often you have your data in a table, and you want to plot them all and arrange them in a nice array. The enhanced VBA procedure in this post kills two birds: creating AND arranging the charts.

This procedure needs a worksheet with the following layout.

The yellow range is named "Settings" (B2:K3 in this example, but it can be any 2-row by 10 column range with the items in this order), and contains many of the adjustable constants from the previous procedure. Placing them in the worksheet makes it easier to adjust them, since you do not need to hunt through the code to change a parameter. The headers in B2:K2 are hard to read in the screen shot, so here they are again, with descriptions:

RowsHigh - Height of each chart, in worksheet rows
ColsWide - Width of each chart, in worksheet columns
FirstRow - Topmost worksheet row covered by charts
FirstCol - Leftmost worksheet column covered by charts
SkipRows - Worksheet rows to skip between rows of charts
SkipCols - Worksheet columns to skip between columns of charts
NAcross - Number of charts per row of charts
FontSize - Font size used in the charts
RowHeight - Height of worksheet rows, in points
ColWidth - Width of worksheet columns, in characters of default font

The green cell is named "DataAll" (B5 in this example). and is the top left cell of a two-row range with the category labels (C5:H5), the series name (B6), and values (C6:H6) for a series which will be plotted on each chart in the grid. The purple cell (B8) is named "DataEach", and is the top left cell of a N+1 row range with category labels (C8:H8), N series names (B9 downward), and N sets of Y values (C9:H9 and downward), for N series that will be plotted in N charts. In this case there are 12 rows of Y values, and they will appear in 12 charts. The DataAll and DataEach ranges don't need to be laid out nicely with respect to each other, but it makes it easier to keep track. They do need to be isolated, with empty rows and columns separating them from other data.

The procedure is shown below. The setup worksheet shown above should be activated before running the procedure. A new worksheet is inserted and the charts arrayed on this new sheet. The procedure steps down the DataEach range

PLAIN TEXT

Visual Basic:

Sub MakeGridOfCharts()
' Jon Peltier 3/19/2008
' inputs
Dim vSettings As Variant
Dim rDataAll As Range
Dim rDataEach As Range
' chart size - adjust as desired
Dim nRowsTall As Long
Dim nColsWide As Long
Dim nFontSize As Double
' chart layout - adjust as desired
Dim nChartsPerRow As Long
Dim nSkipRows As Long
Dim nSkipCols As Long
Dim nFirstRow As Long
Dim nFirstCol As Long
Dim wsData As Worksheet
Dim wsChart As Worksheet
Dim iChart As Long
Dim chtob As ChartObject
Dim srs As Series
Dim dWidth As Double
Dim dHeight As Double
Dim rData As Range
Dim dFirstChartTop As Double
Dim dFirstChartLeft As Double
Dim dRowsBetweenChart As Double
Dim dColsBetweenChart As Double
Dim dRowHeight As Double
Dim dColWidth As Double
Application.ScreenUpdating = False
' define worksheets
Set wsData = ActiveSheet
Set wsChart = Worksheets.Add(After:=wsData)
' define data inputs
vSettings = wsData.Range("Settings").Value
Set rDataAll = wsData.Range("DataAll").CurrentRegion
Set rDataEach = wsData.Range("DataEach").CurrentRegion
' extract layout parameters
nRowsTall = vSettings(2, 1)
nColsWide = vSettings(2, 2)
nFirstRow = vSettings(2, 3)
nFirstCol = vSettings(2, 4)
nSkipRows = vSettings(2, 5)
nSkipCols = vSettings(2, 6)
nChartsPerRow = vSettings(2, 7)
nFontSize = vSettings(2, 8)
dRowHeight = vSettings(2, 9)
dColWidth = vSettings(2, 10)
If dRowHeight> 0 Then
wsChart.Rows.RowHeight = dRowHeight
End If
If dColWidth> 0 Then
wsChart.Columns.ColumnWidth = dColWidth
End If
With wsChart.Cells(1, 1)
dWidth = nColsWide * .Width
dHeight = nRowsTall * .Height
dFirstChartLeft = (nFirstCol - 1) * .Width
dFirstChartTop = (nFirstRow - 1) * .Height
dRowsBetweenChart = nSkipRows * .Height
dColsBetweenChart = nSkipCols * .Width
End With
For iChart = 1 To rDataEach.Rows.Count - 1
' create the chart
Set chtob = wsChart.ChartObjects.Add( _
((iChart - 1) Mod nChartsPerRow) * (dWidth + dColsBetweenChart) + dFirstChartLeft, _
Int((iChart - 1) / nChartsPerRow) * (dHeight + dRowsBetweenChart) + dFirstChartTop, _
dWidth, dHeight)
' add standard series (common to all charts)
Set srs = chtob.Chart.SeriesCollection.NewSeries
With srs
.Name = rDataAll.Cells(2, 1)
.Values = rDataAll.Cells(2, 2).Resize(, rDataAll.Columns.Count - 1)
.XValues = rDataAll.Cells(1, 2).Resize(, rDataAll.Columns.Count - 1)
.ChartType = xlLine
.Border.Weight = xlMedium
End With
' add variable series (unique to each chart)
Set srs = chtob.Chart.SeriesCollection.NewSeries
With srs
.Name = rDataEach.Cells(iChart + 1, 1)
.Values = rDataEach.Cells(iChart + 1, 2).Resize(, rDataEach.Columns.Count - 1)
.XValues = rDataEach.Cells(1, 2).Resize(, rDataEach.Columns.Count - 1)
.ChartType = xlLine
.Border.Weight = xlMedium
End With
' format chart
With chtob.Chart
.HasTitle = True
.ChartTitle.Characters.Text = srs.Name
With .ChartArea
.AutoScaleFont = False
.Font.Size = nFontSize
.Border.ColorIndex = 2
End With
.HasLegend = False
With .Axes(xlValue)
.HasMajorGridlines = False
.Border.ColorIndex = 48
End With
With .Axes(xlCategory)
.Border.ColorIndex = 48
With .TickLabels
.Orientation = xlHorizontal
.Offset = 0
End With
End With
With .PlotArea
.Border.ColorIndex = 48
.Interior.ColorIndex = 2
.Left = 0
.Top = 0
.Height = chtob.Chart.ChartArea.Height
.Width = chtob.Chart.ChartArea.Width - 7
End With
With .ChartTitle
.Font.Bold = True
.Top = chtob.Chart.PlotArea.InsideTop
.Left = chtob.Chart.PlotArea.InsideLeft + 3
End With
End With
Next
Application.ScreenUpdating = True
End Sub