How to resize charts using Excel VBA ?

There are lot of charts in excel in different width and length sizes. I need to resize them to same size, in order to use them in specific document, presentation or on website. To resize charts, i will have to select each chart and if there are more than 6 charts, it will become difficult to select charts by scrolling or keeping the Ctrl key pressed during selection.

Also, if i have charts in multiple sheets, i will have follow the below steps for each sheet, which will be really a time consuming task.

1. Select all charts on sheet, by first selecting one chart and then select other using mouse click by keeping Ctrl key pressed
2. Go to chart tools or Drawing tools(in case multiple charts are selected) menu
3. Click format
4. Check on the size menu on the right end of menu bar
5. Change the shape height and shape width to the required size
6. Done

Follow above steps on each sheet independently.

Instead of spending time on each sheet, we can write a VBA macro which can be used to resize all charts at once, on all sheets.

–Code start

Sub ResizeCharts()

‘ Define the chart variable as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject

‘Write FOR NEXT loop to select each chart and rename in active sheet
For Each chart In ActiveSheet.ChartObjects

‘Using With and End with method to execute the required code, till the FOR NEXT loop is completed
With chart.Parent

‘ change the numbers in the below brackets (2) to change the size of the chart. The number reflect the chart size in inches
chart.Height = Application.InchesToPoints(2)
chart.Width = Application.InchesToPoints(4)

End With

Next

MsgBox “All charts in the active sheet are resized !”

End Sub

–Code end

Using the above code we can resize all charts in active sheet to same sizes. If you observe above code, there are two lines which decide the size of charts and we only need to change the numbers between brackets (x). In above code, we have option to resize the size in inches. If you are not comfortable with inches, we can tweak the above code by using centimeters (cm). Observe below two lines of codes to tweak.

‘Inches (InchesToPoints)
chart.Height = Application.InchesToPoints(2)
chart.Width = Application.InchesToPoints(4)

‘Centimeters (CentimetersToPoints)
chart.Height = Application.CentimetersToPoints(6)
chart.Width = Application.CentimetersToPoints(12)

So, now we will be able to use the above code to resize all charts on sheet. If we want to use the above code to resize all charts in all worksheets within a workbook, we can further write a FOR NEXT loop to run for each worksheet and execute the above code for each worksheet. Check this link on how to write FOR NEXT Loop.

Thank you for reading. Happy learning !

Please write comments if you like my posts and also write if you want me to post solutions on any problems which you face on excel.