Excel 2010 Bible

Chapter 44: VBA Examples

897

Sub CloseAllWorkbooks()

Dim Book As Workbook

For Each Book In Workbooks

If Book.Name <> ThisWorkbook.Name Then

Book.Close savechanges:=True

End If

Next Book

ThisWorkbook.Close savechanges:=True

End Sub

The procedure uses an If statement within the For-Next loop to determine whether the work-

book is the workbook that contains the code. This is necessary because closing the workbook that

contains the procedure would end the code, and subsequent workbooks would not be affected.

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 per-

form some routine chart editing. You may be surprised by the amount of code that’s generated.

When you understand how objects function within in a chart, however, you can create some use-

ful 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 references,” later in

this chapter). For example, the following statement creates an object variable (MyChart) for the

embedded chart named Chart 1 on the active sheet.

Dim MyChart As Chart

Set MyChart = ActiveSheet.ChartObjects(“Chart 1”)

The following sections contain examples of macros that work with charts.

On the CD
These macros are available on the companion CD-ROM. The file is named chart macros.xlsm.