101 Ready-To-Use Excel Macros

For those of us tasked with building dashboards and reports, charts are a daily part of our work life. However, few of us have had the inclination to automate any aspect of our chart work with macros. Many of us would say that there are too many scope changes and iterative adjustments in the normal reporting environment to automate charting.

On many levels, that is true, but some aspects of our work lend themselves to a bit of automation. In this Part, we explore a handful of charting macros that can help you save time and become a bit more efficient.

The code for this Part can be found on this book's companion website. See this book's Introduction for more on the companion website.

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 collection has an index number that you can use to bring it into focus. For example, ChartObjects(1) points to the first chart in the sheet.

In this macro, we use this concept to loop through the charts ...