Macro 78: Resize All Charts on a Worksheet
When building a dashboard, you often want to achieve some level of symmetry and balance. This
sometimes requires some level of chart size standardization. The macro in this section gives you an
easy way to set a standard height and width for all your charts at once.
How it works
All charts belong to the ChartObjects collection. To take an action on all charts at one time, you
simply iterate through all the charts in ChartObjects. Each chart in the ChartObjects collec-
tion has an index number that you can use to bring it into focus. For example, Chart
Objects(1) points to the first chart in the sheet.
In this macro, we use this concept to loop through the charts on the active sheet with a simple counter.
Each time a new chart is brought into focus, we change its height and width to the size we’ve defined.
Sub Macro78()
‘Step 1: Declare your variables
Dim i As Integer
‘Step 2: Start Looping through all the charts
For i = 1 To ActiveSheet.ChartObjects.Count
‘Step 3: Activate each chart and size
With ActiveSheet.ChartObjects(i)
.Width = 300
.Height = 200
End With
‘Step 4: Increment to move to next chart
Next i
End Sub
1. Step 1 declares an integer object that is used as a looping mechanism. We call the
variable i.
2. Step 2 starts the looping by setting i to count from 1 to the maximum number of charts in
the ChartObjects collection on the active sheet. When the code starts, i initiates with
the number 1. As we loop, the variable increments up one number until it reaches a number
equal to the maximum number of charts on the sheet.
3. Step 3 passes i to the ChartObjects collection as the index number. This brings a chart
into focus. We then set the width and height of the chart to the number we specify here in
the code. You can change these numbers to suit your needs.
4. In Step 4, the macro loops back around to increment i up one number and get the next
chart. After all charts have been evaluated, the macro ends.
How to use it
To implement this macro, you can copy and paste it into a standard module:
1. Activate the Visual Basic Editor by pressing ALT+F11.
2. Right-click the project/workbook name in the Project window.
3. Choose Insert➜Module.
4. Type or paste the code into the newly created blank module.
