Resize All Charts Macro – Simultaneously, Easy and Quick

This handy Excel macro resizes all charts in your workbook, to your specified size. No more manually resizing each chart to fit your reports – this is a great macro that I use every day. In fact, it is perfect to add to your Personal Macro Workbook.

 

Steps

1. Create Macro Enabled Workbook

2. Write Code - Dimension, Input Box, Loop

3. Done

 

 

Create a Macro Enabled Workbook

Same process for all Macros!

1.       Open Excel.

2.       Create “Blank workbook”.

3.       Go to “File”, and click “Save As”.

4.       Click “Browse”, name it anything but make sure you select “Excel Macro-Enabled Workbook”.

Save-macro-Enabled-Workbook.png

5.       Click the “Developer” pane and click “Visual Basic” (or Alt+F11).

6.       Right click on “ThisWorkbook”, “Insert” and “Module”.

Create_Module_VBA.png

Add in the Resize Chart Macro Code

Now we will create the code – this is a simple macro but uses cool features such as the input box. Follow step by step or skip below for the full code.

1.       In your module, create you macro by starting with:

Sub Resize_Charts()

End Sub

2.       You now need to declare variable names and their types. This is something you will use in all your macros, and is very easy to get the hang of.

3.       We will have three variables – i (number of charts in sheet), h(height of chart), w(width of chart).

4. We need to tell Excel what dimension the variables are. For example are they integers or string? They are all integers in this case, however since I want h and w to potentially be decimal inputs, I declare this as “Double”.

Sub Resize_Charts()
Dim i As Integer
Dim h As Double
Dim w As Double

End Sub

5.       Now we need to tell Excel what the w and h variable are. These will both be input boxes, and all you need to do is add in the text that displays next to the input box.

Sub Resize_Charts()
Dim i As Integer
Dim h As Double
Dim w As Double

w = InputBox("Enter Width (inch)", "Excellen - Resize Charts")
h = InputBox("Enter Height(inch)", "Excellen - Resize Charts")

End Sub
Input-box-text.png

6.       Now we need the macro to iterate through each chart, and change the width and height to the w and h value. i is the number of iterations to do. We tell Excel to make this number the total count of Chart objects in the sheet. The macro then goes through each chart, and resizes it.

Sub Resize_Charts()
Dim i As Integer
Dim h As Double
Dim w As Double

w = InputBox("Enter Width (inch)", "Excellen - Resize Charts")
h = InputBox("Enter Height(inch)", "Excellen - Resize Charts")

For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)

'Do something

End With
Next i

End Sub

7.       The command to resize chart objects is “.Width” and “.Height”, so we could simply do say .Width = w. However, “.Width” is in “Points”. So I need to convert our input of inches to points. There are 72 points in an inch, so I simply multiply w by 72!

Sub Resize_Chartss()
Dim i As Integer
Dim h As Double
Dim w As Double

w = InputBox("Enter Width (inch)", "Excellen - Resize Charts")
h = InputBox("Enter Height(inch)", "Excellen - Resize Charts")

For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)

.Width = (w * 72)  
.Height = (h * 72) 

End With
Next i

End Sub

8.       If you want it in cm, multiply by 28.3464567

9.       If you want it in mm, multiply by 2.834646

10.       To change your units of measurement, go to “File”, “Options”, “Advanced” and scroll down to “Display” where you will find “Ruler units”.

excel-change-ruler-measurement.png

11.       Finally, I add in an Iferror statement, else if you quit out of the input box you get an error.

Final Code

Sub Resize_Charts()

    'Tell Excel what dimensions your variables are
    Dim i As Integer
    Dim h As Double
    Dim w As Double
    
    'Define your variables as input from input box
    On Error GoTo errorstatement 'Go to this line if error
    w = InputBox("Enter Width (inch)", "Excellen - Resize Charts")
    h = InputBox("Enter Height(inch)", "Excellen - Resize Charts")
    
    'Iterate through each chart on the Active Sheet
    For i = 1 To ActiveSheet.ChartObjects.Count
    With ActiveSheet.ChartObjects(i)
    .Width = (w * 72)  'Changing Points to Pixels (72 points per inch)
    .Height = (h * 72) 'Changing Points to Pixels (72 points per inch)
    End With
    Next i
    
    Exit Sub 'If this wasn't here, it would show the below error message

errorstatement:
    MsgBox "Error Occured (Probably No Entry) - " & Err.Description 'Delete this line if you just want an error to end macro

End Sub

Resize Away!

It’s as simple as that, you can now exactly resize all your charts to whatever measurement you want. Just look up the conversion to points on Google.

The original macro in my list of Useful Personal Workbook Macros was simply:

Sub Resize_Charts()
Dim i As Integer
For i = 1 To ActiveSheet.ChartObjects.Count
With ActiveSheet.ChartObjects(i)
.Width = 300
.Height = 200
End With
Next i
End Sub

With basic understanding of dimensions and loops, I easily upgraded this into something much more functional to suit my needs. I recommend you take the time to learn these concepts, and then in no time you will be modifying and writing your own macros!

Start by using the above link to find macros you can start editing!

4. Download

More templates