Chapter 47: Seeing Some VBA Examples
47
Working with Charts
Manipulating charts with VBA can be confusing, mainly because of the large number of
objects involved. To get a feel for working with charts, turn on the macro recorder, create a
chart, and perform some routine chart editing. You may be surprised by the amount of code
that’s generated.
When you understand the way that objects function in a chart, however, you can cre-
ate some useful macros. This section presents a few macros that deal with charts. When
you write macros that manipulate charts, you need to understand some terminology. An
embedded chart on a worksheet is a ChartObject object, and the ChartObject contains
the actual Chart object. A chart on a chart sheet, on the other hand, does not have a
ChartObject container.
It’s often useful to create an object reference to a chart. (See “Simplifying object refer-
ences” later in this chapter.) For example, the following statements declare an object vari-
able (MyChart) and assign the embedded chart named Chart 1 on the active sheet to it:
Dim MyChart As Chart
Set MyChart = ActiveSheet.ChartObjects("Chart 1").ChartThe following sections contain examples of macros that work with charts.
These macros are available on this book’s website at http://www.wiley.com/go/excel2019bible.
The file is named chart macros.xlsm.Modifying the chart type
The following example changes the chart type of every embedded chart on the active
sheet. It makes each chart a clustered column chart by adjusting the ChartType property
of the Chart object. A built-in constant, xlColumnClustered, represents a standard col-
umn chart:
Sub ChartType()
Dim ChtObj As ChartObject
For Each ChtObj In ActiveSheet.ChartObjects
ChtObj.Chart.ChartType = xlColumnClustered
Next ChtObj
End SubThe preceding example uses a For-Next loop to cycle through all of the ChartObject
objects on the active sheet. Within the loop, the chart type is assigned a new value, mak-
ing it a column chart.
The following macro performs the same function but works on all chart sheets in the active
workbook:
Sub ChartType2()
Dim Cht As Chart