excel-vba Tutorial => Arranging Charts into a Grid

Example

A common chore with charts in Excel is standardizing the size and layout of multiple charts on a single sheet. If done manually, you can hold down ALT while resizing or moving the chart to "stick" to cell boundaries. This works for a couple charts, but a VBA approach is much simpler.

Code to create a grid

This code will create a grid of charts starting at a given (Top, Left) position, with a defined number of columns, and a defined common chart size. The charts will be placed in the order they were created and wrap around the edge to form a new row.

Sub CreateGridOfCharts()

    Dim int_cols As Integer
    int_cols = 3
    
    Dim cht_width As Double
    cht_width = 250
        
    Dim cht_height As Double
    cht_height = 200
    
    Dim offset_vertical As Double
    offset_vertical = 195
    
    Dim offset_horz As Double
    offset_horz = 40

    Dim sht As Worksheet
    Set sht = ActiveSheet

    Dim count As Integer
    count = 0
    
    'iterate through ChartObjects on current sheet
    Dim cht_obj As ChartObject
    For Each cht_obj In sht.ChartObjects
        
        'use integer division and Mod to get position in grid
        cht_obj.Top = (count \ int_cols) * cht_height + offset_vertical
        cht_obj.Left = (count Mod int_cols) * cht_width + offset_horz
        cht_obj.Width = cht_width
        cht_obj.Height = cht_height

        count = count + 1

    Next cht_obj
End Sub

Result with several charts

These pictures show the original random layout of charts and the resulting grid from running the code above.

Before