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
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
By adjusting the values in the yellow cells, you can change the output sheet remarkably. This is starting to look like a dashboard.