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.

Related Posts:

• Arranging Charts in a Grid
• Dynamic Chart using Pivot Table and VBA
• Build an Excel Add-In 2 – Enhanced Functionality
• Stack Columns In Order Of Size With VBA
• Extract Chart Data
• Build an Excel Add-In 3 – Auxiliary Modular Functions
• Update Regular Chart when Pivot Table Updates
• Pivot Table Conditional Formatting with VBA
• Quick VBA Routine: XY Chart with Axis Titles
• Build an Excel Add-In 1 – Basic Routine