Calling a Procedure from Another Project

You can call a procedure located in any module in the same project by specifying the procedure name.

Let's suppose that the procedure FormulasOnOff is located in another module in the same project as the WhatsInACell macro. To call the procedure FormulasOnOff from the WhatsInACell macro, all you need to do is specify the procedure name, as shown in the following example:

Sub WhatsInACell()

<place recorded macro instruction here> FormulasOnOff End Sub

However, if two or more modules contain a procedure with the same name, you must include the module name in addition to the procedure name.

Let's suppose that the FirstSteps (Chap01.xls) project has three modules. Module FormulaFormatting contains the WhatsInACell macro, while module Switches and module Formulas both contain the FormulasOnOff macro. To call FormulasOnOff (located in the Switches module) from the WhatsInACell macro, precede the procedure name with the module name, as shown in the following example:

Sub WhatsInACell()

<place recorded macro instruction here> Switches.FormulasOnOff End Sub

To call a procedure from a different project, you must set up the reference to the project. You do this in the References dialog box. Because the FormulasOnOff macro is located in the Personal (Personal.xls) project, before you can call this macro from the WhatsInACell macro, you must add the reference to the Personal project in the following way:

1. In the Project Explorer window, click FirstSteps (Chap01.xls).

2. Choose Tools | References.

3. In the References dialog box, click the check box next to Personal (Figure 2-3). Then click OK.

References - FirstSteps

Available References:

V" Visual Basic For Appfications V; Microsoft Excel 9.0 Object Library S OLE Automation •s Microsoft Office 9,0 Object Lforary

□ Crystal Report 8 ActiveX Designer Run Time Library [_ Crystal Report 8 Standard Wfeard library

F . Microsoft DAO 3,6 Object Library

□ Microsoft Forms 2,0 Object Library

□ Ref Edit Control

□ VBAProjett ! 'VBAProject

IAS Heloer COM Component 1,0 Tvüe übrarv jlJ___I

Priority

Help

Personal

Location: C:\Documents and Settings\Julitta Korol\AppÜcation Data\Micn Larx)uage: English/United States

Figure 2-3:

The References dialog box lists all the references available to your project. If you want to execute a procedure located in a different project, you must establish a reference to the other project.

Now that the reference to the Personal project has been established, let's call the FormulasOnOff macro from the WhatsInACell procedure.

1. In the Project Explorer window, select FirstSteps (Chap01.xls) and locate the module with the WhatsInACell procedure.

2. Enter a new line before MsgBox "All actions have been performed" and type the following line of code: FormulasOnOff.

3. Return to the Microsoft Excel window and make sure that Sheet1 contains the example spreadsheet (see Figure 1-1 in Chapter 1).

4. Run the WhatsInACell macro using any of the techniques you learned in Chapter 1.

If you give the same name to two different procedures in two different projects, you must specify a project name when you call that procedure.

Let's suppose that the FormulasOnOff macro is located both in the FirstSteps (Chap01.xls) project and in the Personal (Personal.xls) project. To call the FormulasOnOff macro in the Personal (Personal.xls) project (remember that the reference to the Personal project must be established first), include the project name:

Sub WhatsInACell ( )

<place recorded macro instruction here> Personal.Switches.FormulasOnOff End Sub

Tip 2-2: How Visual Basic Tip 2-3: Project Name is Missing Locates the Called Procedure in the References Dialog Box

When you call a procedure, Visual Basic first looks for it in the same module where the calling procedure (WhatsInACell) is located. If the called procedure (FormulasOnOff) is not found in the same module, Visual Basic searches other modules in the same project. If the procedure still can't be found, Visual Basic checks the references to other projects.

If you want to call a procedure from a project that is currently closed, when you open the References dialog to establish the reference to this project, the name of the project is not listed. Click the Browse button, and open the folder where the project is located. By default, the Add Reference dialog box lists Library Files (*.olb, .tlb, .dll). Choose Microsoft Excel Files (*.xls, *.xla) from the Files of Type drop-down list, select the file that contains the procedure you want to set the reference to, and click Open. The name of the project will be added as the last entry in the References dialog box.

+1 0

Post a comment