Peltier Tech Excel Charts and Programming Blog

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.

One effective way to present charts is to array them on a worksheet. You can lay them out manually, and even line them up nicely: by holding ALT while dragging and resizing charts, you constrain them to stick to the grid of cell boundaries.

However, if you have a lot of charts, this is tedious. I’ve arranged the charts in the view below using a VBA procedure.

The VBA procedure is not very complicated, and it lets you specify the height and width of each chart in cell units, as well as the top left cell under the first chart, and the number of rows and columns between adjacent charts.

Sub MakeGridOfCharts()
  ' Jon Peltier (3/19/2008, mod 10/03/2014)
  ' https://peltiertech.com/

  ' chart size - adjust as desired
  ' set one or both to zero to use dimensions of active chart
  '   (or first chart if no chart is active)
  Const nRowsTall As Long = 6
  Const nColsWide As Long = 3

  ' chart layout - adjust as desired
  Const nChartsPerRow As Long = 3
  Const nSkipRows As Long = 2
  Const nSkipCols As Long = 1
  Const nFirstRow As Long = 3
  Const nFirstCol As Long = 2

  Dim iChart As Long
  Dim chtob As ChartObject
  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

  If ActiveSheet.ChartObjects.Count > 0 Then

    With ActiveSheet.Cells(nFirstRow, nFirstCol)
      If nRowsTall * nColsWide > 0 Then
        dWidth = nColsWide * .Width
        dHeight = nRowsTall * .Height
      Else
        If Not ActiveChart Is Nothing Then
          Set chtob = ActiveChart.Parent
        Else
          Set chtob = ActiveSheet.ChartObjects(1)
        End If
        dWidth = chtob.Width
        dHeight = chtob.Height
      End If

      dFirstChartLeft = .Left
      dFirstChartTop = .Top
      dRowsBetweenChart = nSkipRows * .Height
      dColsBetweenChart = nSkipCols * .Width
    End With

    For iChart = 1 To ActiveSheet.ChartObjects.Count

      Set chtob = ActiveSheet.ChartObjects(iChart)

      With chtob
        .Left = ((iChart - 1) Mod nChartsPerRow) * _
            (dWidth + dColsBetweenChart) + dFirstChartLeft
        .Top = Int((iChart - 1) / nChartsPerRow) * _
            (dHeight + dRowsBetweenChart) + dFirstChartTop
        .Width = dWidth
        .Height = dHeight
      End With

    Next

  End If

End Sub