Page 63 - Excel Progamming Weekend Crash Course

d540629 ch03.qxd 9/2/03 9:27 AM Page 38

38 Friday Evening

The following code example shows how to use a For Next In loop to close all open
workbooks, saving any changes.
Dim WB As Workbook
For Each WB in Workbooks
WB.Close SaveChanges:=True
Next

Referencing Workbooks
To manipulate a workbook in code, you must have a reference to it. There are several ways
to do this, and the one you use depends on your specific situation.
One method is to assign a reference when you either create or open the workbook with
the Add or Open methods, respectively. Thus:
Dim MyNewWB As Workbook
Dim MyOpenedWB As Workbook
Set MyNewWB = Workbooks.Add
Set MyOpenedWB = Workbooks.Open(“expenses.xls”)
Note that a variable used for an object reference must be created with the proper type.

For details on creating an object reference variable with the proper type,
turn to Session 4.
Cross-Ref
If the workbook already has been created or opened — in other words, if it already exists
in the Workbooks collection — you can reference it directly from the collection. Thus, the
code

Workbooks(Name)
returns a reference to the workbook with the specified name. Note that the name must
include the extension: sales.xls and not just sales. If the specified workbook does not
exist, an error occurs.
Finally, VBA provides a couple of special keywords that can be used to refer to work-
books:
ActiveWorkbook. This references the active workbook.
ThisWorkbook. This references the workbook in which the code is running.

The concept of the active workbook is easy to understand: It represents the workbook
that is active and on-screen. The ActiveWorkbook keyword can be extremely useful.
The need for ThisWorkbook, however, may not be clear. In many situations, the VBA
code that is executing is located in the same workbook that’s being manipulated by the
code, and there is no need for ThisWorkbook. At times, however, code that is located in
one workbook is manipulating data in another workbook. When such code needs to refer
to the workbook it is in rather than to the workbook that is being manipulated, it uses
the ThisWorkbook keyword. This situation arises most often when you are programming