Page 122 - Microsoft Office Excel 2003 Programming Inside Out

Microsoft Office Excel 2003 Programming Inside Out

One limitation of the Application.Run method is that the workbook that contains the called
procedure must be open to allow Excel to look into it to see what procedures are available.
That requirement is the reason that the Macros dialog box can display macros in the current
workbook, any open workbook, or all open workbooks: the program designers chose not to
let Excel reach into workbooks unless they were already open and ready for use.
You can, however, make it possible to call procedures in any workbook, open or not, by
creating a reference to the procedure. Just as using the Application.Run method to call a pro­
cedure from another workbook is similar to creating a link from a worksheet cell to a cell in
another workbook, creating a reference to a procedure in another workbook is very similar to
linking or embedding an outside file in an Excel workbook. But, instead of using the Insert
Object dialog box in Excel, you use the Tools, References dialog box in the Visual Basic Editor
(shown in Figure 5-4).

Figure 5-4. The References dialog box lists all the available resources to which you can link
and subsequently call procedures.

When you first display the References dialog box, it doesn’t list the projects available in the
Excel workbooks on your computer (and any network drives to which you have access), but
you can go looking for them using the Add Reference dialog box.

To add a reference to an Excel workbook to the list in the References dialog box, follow
Chapter 5
these steps:
1 Click Tools, References.
2 Click Browse.
3 Click the Files of Type down arrow, Microsoft Office Excel Files (*.xls;*.xla).
4 Navigate to the directory containing the workbook, click the workbook, and click Open.

96
Part 2: Visual Basic for Applications