Executing a procedure from another procedure

One of the most common ways to execute a procedure is from another VBA 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. The Run method is useful when you need to run a procedure whose 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

Tip 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 to 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)

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, 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 within the project. 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-3), 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.

Referantei. VBAProjett

V * Sjaf Ö49C Per W15 v MtTowftEicE ¿-»TOtiifitLtirarv

I ILMSAYED: VBAPnoJeet

IAS Htfctf iSfflpene11LD Type i&ttV HS RADIUS ftOhKO LOTvpelfcwv I J.QType Lfrary

Centre Lbrtrt I ArtWEMo™ ■IWTTQ- ttffK Itrafv

I lAct™eXKM.Rfi06lrfLl>farii

.i")Li)t Standard

Prien fy

Figure 9-3: 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 Project Properties dialog box. Click the project name in the Project window and then choose Tools xxxx Properties (where xxxx is the current project name). In the Project Properties dialog box, click the General tab and change the name displayed in the Project Name field.

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 2007 workbooks always include references to the following object libraries:

■ Visual Basic for Applications

■ Microsoft Excel 12.0 Object Library

■ OLE Automation

■ Microsoft Office 12.0 Object Library

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

Note 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.

Why Call Other Procedures?

If you're new to programming, you may wonder why anyone would ever want to call a procedure from another procedure. You may ask, "Why not just put the code from the called procedure into the calling procedure and keep things simple?"

One reason is to clarify your code. The simpler your code, the easier it is to maintain and modify. Smaller routines are easier to decipher and then debug. Examine the accompanying procedure, which does nothing but call other procedures. This procedure is very easy to follow.

Sub Main()

Call GetUserOptions Call ProcessData Call CleanUp Call CloseltDown End Sub

Calling other procedures also eliminates redundancy. Suppose that you need to perform an operation at ten different places in your routine. Rather than enter the code ten times, you can write a procedure to perform the operation and then simply call the procedure ten times.

Also, you may have a series of general-purpose procedures that you use frequently. If you store these in a separate module, you can import the module to your current project and then call these procedures as needed - which is much easier than copying and pasting the code into your new procedures.

Creating several small procedures rather than a single large one is often considered good programming practice. A modular approach not only makes your job easier but also makes life easier for the people who wind up working with your code._

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 workbook is to use the Run method of the Application object. This technique does not require that you establish a reference, but the workbook that contains the procedure must be open. The following statement executes the Consolidate procedure located in a workbook named budget macros. xlsm:

Application.Run "'budget macros.xlsm' ! Consolidate"

0 0

Responses

Post a comment