Executing a procedure from another procedure

One of the most common ways to execute a procedure is from another procedure. You have three ways to do this:

♦ Enter the procedure's name, followed by its arguments (if any), separated by commas.

♦ Use the Call keyword followed by the procedure's name and then its arguments (if any), enclosed in parentheses and separated by commas.

♦ Use the Run method of the Application object. You can use this method to execute other VBA procedures or XLM macros. The Run method is useful when you need to run a procedure, and the procedure's name is assigned to a variable. You can then pass the variable as an argument to the Run method.

The following example demonstrates the first method. In this case, the MySub procedure processes some statements (not shown), executes the UpdateSheet procedure, and then executes the rest of the statements.

Sub MySub()

Sub UpdateSheet() ' ... [code goes here] ... End Sub

The following example demonstrates the second method. The Call keyword executes the Update procedure, which requires one argument; the calling procedure passes the argument to the called procedure. I discuss procedure arguments later in this chapter (see "Passing Arguments to Procedures").

Sub MySub()

MonthNum = InputBox("Enter the month number: ") Call UpdateSheet(MonthNum) ' ... [code goes here] ... End Sub

Sub UpdateSheet(MonthSeq) ' ... [code goes here] ... End Sub

Even though it's optional,some programmers always use the Call keyword just to make it perfectly clear that another procedure is being called.

The next example uses the Run method to execute the UpdateSheet procedure and then pass MonthNum as the argument:

Sub MySub()

MonthNum = InputBox("Enter the month number: ") Application.Run "UpdateSheet", MonthNum ' ... [code goes here] ... End Sub

Sub UpdateSheet(MonthSeq) ' ... [code goes here] ... End Sub

Perhaps the best reason to use the Run method is when the procedure name is assigned to a variable. In fact, it's the only way to execute a procedure in such a way. The following example demonstrates this. The Main procedure uses the VBA WeekDay function to determine the day of the week (an integer between 1 and 7, beginning with Sunday). The SubToCall variable is assigned a string that represents a procedure name. The Run method then calls the appropriate procedure (either WeekEnd or Daily).

Sub Main()

Dim SubToCall As String Select Case WeekDay(Now)

Case 1: SubToCall = "WeekEnd" Case 7: SubToCall = "WeekEnd" Case Else: SubToCall = "Daily" End Select

Application.Run SubToCall

End Sub

Sub WeekEnd()

MsgBox "Today is a weekend" ' Code to execute on the weekend ' goes here End Sub

Sub Daily()

MsgBox "Today is not a weekend" ' Code to execute on the weekdays ' goes here End Sub

CALLING A PROCEDURE IN A DIFFERENT MODULE

If VBA can't locate a called procedure in the current module, it looks for public procedures in other modules in the same project.

If you need to call a private procedure from another procedure, both procedures must reside in the same module.

You can't have two procedures with the same name in the same module, but you can have identically named procedures in different modules. You can force VBA to execute an ambiguously named procedure — that is, another procedure in a different module that has the same name. To do so, precede the procedure name with the module name and a dot. For example, say that you define procedures named MySub in Module1 and Module2. If you want a procedure in Module2 to call the MySub in Module1, you can use either of the following statements:

Module1.MySub Call Module1.MySub

If you do not differentiate between procedures that have the same name, you get an Ambiguous name detected error message.

CALLING A PROCEDURE IN A DIFFERENT WORKBOOK

In some cases, you may need your procedure to execute another procedure defined in a different workbook. To do so, you have two options: Either establish a reference to the other workbook, or use the Run method and specify the workbook name explicitly.

To add a reference to another workbook, choose the VBE's Tools ^ References command. Excel displays the References dialog box (see Figure 9-4), which lists all available references, including all open workbooks. Simply check the box that corresponds to the workbook that you want to add as a reference and then click OK. After you establish a reference, you can call procedures in the workbook as if they were in the same workbook as the calling procedure.

Figure 9-4: The References dialog box lets you establish a reference to another workbook.

A referenced workbook does not have to be open; it is treated like a separate object library. Use the Browse button in the References dialog box to establish a reference to a workbook that isn't open. The workbook names that appear in the list of references are listed by their VBE project names. By default, every project is initially named VBAProject. Therefore, the list may contain several identically named items. To distinguish a project, change its name in the Properties window of the VBE. The list of references displayed in the References dialog box also includes object libraries and ActiveX controls that are registered on your system. Your Excel 2003 workbooks always include references to the following object libraries:

♦ Visual Basic for Applications

♦ Microsoft Excel 11.0 Object Library

♦ OLE Automation

♦ Microsoft Office 11.0 Object Library

♦ Microsoft Forms 2.0 Object Library (optional, included only if your project includes a UserForm)

Any additional references to other workbooks that you add are also listed in your project outline in the Project Explorer window in the VBE. These references are listed under a node called References.

If you've established a reference to a workbook that contains the procedure YourSub, for example, you can use either of the following statements to call

YourSub:

YourSub Call YourSub

To precisely identify a procedure in a different workbook, specify the project name, module name, and procedure name by using the following syntax:

MyProject.MyModule.MySub

Alternatively, you can use the Call keyword:

Call MyProject.MyModule.MySub

Another way to call a procedure in a different open workbook is to use the Run method of the Application object. This technique does not require that you establish a reference. The following statement executes the Consolidate procedure located in a workbook named budget macros.xls:

Application.Run "'budget macros.xls'lConsolidate"

Was this article helpful?

0 0

Responses

Post a comment