To create a reference to a workbookFor a procedure to be called from outside the module in which it resides, two conditions must be met:
If procedures with duplicate names exist in different modules or workbooks, you may also need to include a module qualifier in front of the procedure name to distinguish between procedures.
The following sections describe how to create a reference to a workbook and how to use module qualifiers. For more information about specifying procedure scope, see "Specifying Procedure Scope" earlier in this chapter.
If you want to call a procedure in another workbook, you must create a reference to the workbook that contains the procedure. A referenced workbook need not be visible, or even open, for you to be able to call procedures in it.
To create a reference to a workbook
1. Open the workbook that contains the calling procedure, and activate a module sheet.
2. On the Tools menu, click References.
3. The Available References box shows all open workbooks and all workbooks referenced by an open workbook.
4. Select the check box next to the name of the workbook you want to create a reference to.
5. To select a workbook that's not listed in the Available References box, click Browse, and then locate the workbook you want to create a reference to.
If one workbook has an indirect reference link to another workbook, Visual Basic allows the procedures in the first workbook to call procedures in the other workbook by specifying the name of the referenced workbook in the calling statement. For example, suppose that the workbook FirstBk has a reference link to SecondBk, which has a reference link to ThirdBk. A procedure in FirstBk could call the procedure TestProc in ThirdBk using the code in the following example.
[THIRDBK.XLS].TestProc
You cannot have circular references to workbooks. That is, a workbook that references another workbook cannot in turn be referenced by that other workbook.
Tip
You cannot create a link to a workbook programmatically; you must manually create a reference link in each workbook that needs access to procedures in another workbook. One way around this is to create a template that contains a link to the library workbook and then create new workbooks based on that template. Each new workbook will already contain a link to the library workbook.
If you want every new workbook you create to contain a reference link to a certain workbook, create a template that contains a link to that workbook, and save the template as Book.xlt in your Xlstart folder or your alternate startup folder. Every new workbook you create will then be based on this template by default.
If there are procedures with duplicate names in different modules or in different workbooks, you may need to include a module qualifier before the procedure name when calling the procedure. If the procedures are in different modules in the same workbook, the module qualifier should have the syntax shown in the following line.
[modulename].procedurename
For example, the following code calls two procedures — both named "TotalToDate" — stored in different modules in the same workbook.
[East Coast].TotalToDate MidWest.TotalToDate
The square brackets around the module name are optional if the module name obeys Visual Basic naming conventions (no spaces, no periods, and so on).
If the procedures are in different workbooks, the module qualifier has the syntax shown in the following line.
[workbookname].[modulename].procedurename
For example, the following code calls two procedures — both named "TotalToDate" — stored in different workbooks.
[REGION.XLS].[Total Sales].TotalToDate [STATE.XLS].[Total Sales].TotalToDate
A module qualifier must refer to a module in a workbook referenced by the workbook that contains the calling procedure.
If you don't include a module qualifier to indicate which of the identically named procedures you want to run, Visual Basic searches through modules in the following order, running the first module with that name that it finds: the current module, then other modules in the current workbook, and then modules in referenced workbooks.