Bottom line: Learn how to quickly arrange your charts in a panel chart or shape grid layout with a VBA macro in Excel. Applies to all types of shapes including slicers, images, charts, etc.
Sometimes we want to have multiple rows or columns of shapes and want to display them in a grid layout. This is especially useful for a collection of small charts that we refer to as a panel chart.
I’ve covered manual approaches to achieving this layout in past articles.
Those articles explain how to use the Alignment tools built into Excel. They are great techniques to know, but also take quite a few steps to achieve the grid layout.
So, I created a few macros that automate this process of creating an evenly spaced shape grid. This is a continuation of my previous post on the Auto Space Macros to create a single column (vertical) or row (horizontal) layout.
The Shape Grid Macro
In the video above I explain how to use the Shape Grid macros to create a grid layout for any type of shape. There are separate macros for a horizontal or vertical layout.
Here are the steps for running the macro.
Step 1: Select Multiple Shapes
The macros run on the shapes you have selected in the worksheet. So the first step before running the macro is to select multiple shapes.
Here are a few ways select multiple shapes:
Select one shape, then press Ctrl+A to select all shapes on the sheet.
Select one shape, then hold the Ctrl key and left-click other shapes to select them.
Note: Charts, Slicers, Timelines, Pictures, Images, Icons, 3D Models, SmartArt, Word Art, and Controls (buttons, comboboxes, etc.) are all considered Shapes in Excel. Most of these items are found on the Insert tab of the Ribbon.
Step 2: Specify the Number of Rows or Columns
You can specify the number of rows or columns you want in the grid.
The number of rows or columns in the grid is set with an InputBox. The InputBox will appear when you run the macro, and prompts the user to input the number of rows (horizontal) or columns (vertical).
Step 3: Specify Space Between Shapes
The macros will create an equal space between each shape, and you can control this distance. A second InputBox is used to prompt the user for space (gap width) between each shape.
This number is specified in Points. Points are the same measurement you see in the screentip when adjusting Row Heights or Column Widths in the worksheet.
I like to use a gap width of 8. However, you can use any number. Inputting 0 (zero) in the InputBox will create a grid without any space between the shapes.
The Result
The macro will run and move all selected shapes into a perfectly aligned and evenly distributed grid layout.
It can all be done with a few clicks, and will save you a ton of time!
The macro loops through the collection of selected shapes and moves each shapes below or to the right of the previous shape.
There are a few If statements and a counter variable that determine if the next shape in the loop should be placed in the same row/column or a new row/column.
The shape’s Top, Left, Height, and Width properties are used to move the shapes. These properties are stored in variables for the previous shape’s location & size. The variables are then used to move the next shape below or to the right.
A MaxHeight variable is used to determine the maximum height or width of the shapes in a row or column. This prevents shapes from being overlapped if the shapes are not all the same size.
The Macro Code
You can download the file that contains the VBA code in the downloads section above. The macros are at the bottom of the m_AutoSpace code module in that file.
Here is the code for the macros.
Sub Shape_Grid_Vertical()
'Automatically space and align shapes to create vertical grid.
Dim shp As Shape
Dim lCnt As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim dSPACE As Variant
Dim lRowCnt As Variant
Dim dStart As Double
Dim dMaxHeight As Double
'Check if shapes are selected
If TypeName(Selection) = "Range" Then
MsgBox "Please select shapes before running the macro."
Exit Sub
End If
'Display an input box to ask user for the number of columns in the vertical grid.
lRowCnt = Application.InputBox("Enter the number of columns for the vertical shape grid.", "Vertical Shape Grid", Type:=1)
'Exit macro if user presses cancel
If lRowCnt <= 0 Or lRowCnt = False Then
Exit Sub
End If
'Display an input box to ask user for the amount of space between shapes.
dSPACE = Application.InputBox("Enter the space between shapes in points.", "Vertical Shape Grid", Type:=1)
'Exit macro if user presses cancel
If TypeName(dSPACE) = "Boolean" Then
Exit Sub
End If
'Set variables
lCnt = 1
'Loop through selected shapes (charts, slicers, timelines, etc.)
For Each shp In Selection.ShapeRange
With shp
'If first shape then store left position
If lCnt = 1 Then
dStart = .Left
Else
If lCnt Mod lRowCnt = 1 Or lRowCnt = 1 Then
'New row, move shape down
.Top = dTop + dMaxHeight + dSPACE
.Left = dStart
dMaxHeight = .Height
Else
'Same row, move shape right
.Top = dTop
.Left = dLeft + dWidth + dSPACE
End If
End If
'Store properties of shape for use in moving next shape in the collection.
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
dMaxHeight = WorksheetFunction.Max(dMaxHeight, .Height)
End With
'Add to shape counter
lCnt = lCnt + 1
Next shp
End Sub
Sub Shape_Grid_Horizontal()
'Automatically space and align shapes to create horizontal grid.
Dim shp As Shape
Dim lCnt As Long
Dim dTop As Double
Dim dLeft As Double
Dim dHeight As Double
Dim dWidth As Double
Dim dSPACE As Variant
Dim lColCnt As Variant
Dim lCol As Long
Dim dStart As Double
Dim lRow As Double
Dim dMaxWidth As Double
'Check if shapes are selected
If TypeName(Selection) = "Range" Then
MsgBox "Please select shapes before running the macro."
Exit Sub
End If
'Display an input box to ask user for the number of rows in the horizontal grid.
lColCnt = Application.InputBox("Enter the number of rows for the horizontal shape grid.", "Horizontal Shape Grid", Type:=1)
'Exit macro if user presses cancel
If lColCnt <= 0 Or lColCnt = False Then
Exit Sub
End If
'Display an input box to ask user for the amount of space between shapes.
dSPACE = Application.InputBox("Enter the space between shapes in points.", "Horizontal Shape Grid", Type:=1)
'Exit macro if user presses cancel
If TypeName(dSPACE) = "Boolean" Then
Exit Sub
End If
'Set variables
lCnt = 1
'Loop through selected shapes (charts, slicers, timelines, etc.)
For Each shp In Selection.ShapeRange
With shp
'If first shape then store top position
If lCnt = 1 Then
dStart = .Top
Else
If lCnt Mod lColCnt = 1 Or lColCnt = 1 Then
'New column, move shape right
.Top = dStart
.Left = dLeft + dMaxWidth + dSPACE
dMaxWidth = .Width
Else
'Same column, move shape down
.Top = dTop + dHeight + dSPACE
.Left = dLeft
End If
End If
'Store properties of shape for use in moving next shape in the collection.
dTop = .Top
dLeft = .Left
dHeight = .Height
dWidth = .Width
dMaxWidth = WorksheetFunction.Max(dMaxWidth, .Width)
End With
'Add to shape counter
lCnt = lCnt + 1
Next shp
End Sub
What happens if you have an odd number of shapes?
If you have an odd number of shapes for the number of rows/columns specified, then they will just spill into the next row/column. For example, if you run the Vertical Grid macro for 3 columns and have 8 shapes selected, the last two shapes will be placed in the third row starting from left to right.
Select the Order of the Shapes
One side benefit of using this For Next Loop to loop through all selected shapes (Selection.ShapeRange), is that we can easily re-order the shapes in the grid.
The ShapeRange object is a collection of the selected shapes. The loop will loop through the shapes in the order they are selected. So, we can use the Ctrl key to select the shapes in the order we want them displayed, and then run the macro to create the grid.
If you use Ctrl+A to select all shapes, then the order of the ShapeRange object is from Back to Front. We can see this in the Selection pane (Home tab > Find & Select button > Selection pane…). Running the macro after pressing Ctrl+A will put the shapes in that order.
The order for the Vertical Grid is left-to-right, then down to a new row.
The order for the Horizontal Grid is top-to-bottom, then right to a new column.
Writing formulas can be one of the most time consuming parts of your weekly or monthly Excel task. If you’re working on automating that process with a macro, then you can have VBA write the formula and input it into the cells for you.
Writing formulas in VBA can be a bit tricky at first, so here are 3 tips to help save time and make the process easier.
Tip #1: The Formula Property
The Formula property is a member of the Range object in VBA. We can use it to set/create a formula for a single cell or range of cells.
There are a few requirements for the value of the formula that we set with the Formula property:
The formula is a string of text that is wrapped in quotation marks. The value of the formula must start and end in quotation marks.
The formula string must start with an equal sign = after the first quotation mark.
Here is a simple example of a formula in a macro.
Sub Formula_Property()
'Formula is a string of text wrapped in quotation marks
'Starts with an = sign
Range("B10").Formula = "=SUM(B4:B9)"
End Sub
The Formula property can also be used to read an existing formula in a cell.
Tip #2: Use the Macro Recorder
When your formulas are more complex or contain special characters, they can be more challenging to write in VBA. Fortunately we can use the macro recorder to create the code for us.
Here are the steps to creating the formula property code with the macro recorder.
Turn on the macro recorder (Developer tab > Record Macro)
Type your formula or edit an existing formula.
Press Enter to enter the formula.
The code is created in the macro.
If your formula contains quotation marks or ampersand symbols, the macro recorder will account for this. It creates all the sub-strings and wraps everything in quotes properly. Here is an example.
Sub Macro10()
'Use the macro recorder to create code for complex formulas with
'special characters and relative references
ActiveCell.FormulaR1C1 = "=""Total Sales: "" & TEXT(R[-5]C,""$#,###"")"
End Sub
Tip #3: R1C1 Style Formula Notation
If you use the macro recorder for formulas, you will notices that it creates code with the FormulaR1C1 property.
R1C1 style notation allows us to create both relative (A1), absolute ($A$1), and mixed ($A1, A$1) references in our macro code.
R1C1 stands for Rows and Columns.
Relative References
For relative references we specify the number of rows and columns we want to offset from the cell that the formula is in. The number of rows and columns are referenced in square brackets.
The following would create a reference to a cell that is 3 rows above and 2 rows to the right of the cell that contains the formula.
R[-3]C[2]
Negative numbers go up rows and columns to the left.
Positive numbers go down rows and columns to the right.
Absolute References
We can also use R1C1 notation for absolute references. This would typically look like $A$2.
For absolute references we do NOT use the square brackets. The following would create a direct reference to cell $A$2, row 2 column 1
R2C1
Mixed References
with mixed references we add the square brackets for either the row or column reference, and no brackets for the other reference. The following formula in cell B2 would create this reference to A$2, where the row is absolute and the column is relative.
R2C[-1]
When creating mixed references, the relative row or column number will depend on what cell the formula is in.
It’s easiest to just use the macro recorder to figure these out.
FormulaR1C1 Property versus Formula Property
The FormulaR1C1 property reads the R1C1 notation and creates the proper references in the cells. If you use the regular Formula property with R1C1 notation, then VBA will attempt to put those letters in the formula, and it will likely result in a formula error.
Therefore, use the Formula property when your code contains cell references ($A$1), the FormulaR1C1 property when you need relative references that are applied to multiple cells or dependent on where the formula is entered.
If your spreadsheet changes based on conditions outside your control, like new columns or rows of data are imported from the data source, then relative references and R1C1 style notation will probably be best.
I hope those tips help. Please leave a comment below with questions or suggestions.