Executing Sub Procedures


In this section, I describe the various ways to execute, or call, a VBA Sub procedure:

  • With the Run Sub/UserForm command (in the VBE). Or you can press the F5 shortcut key or use the Run Sub/UserForm button on the Standard toolbar.

  • From Excel's Macro dialog box.

  • By using the Ctrl key shortcut assigned to the procedure ( assuming that you assigned one).

  • By clicking a button or a shape on a worksheet. The button or shape must have the procedure assigned to it.

  • From another procedure that you write. Sub and Function procedures can execute other procedures.

  • From a custom control in the Ribbon. In addition, built-in Ribbon controls can be "repurposed" to execute a macro.

  • From a customized shortcut menu.

  • When an event occurs. These events include opening the workbook, saving the workbook, closing the workbook, changing a cell 's value, activating a sheet, and many other things.

  • From the Immediate window in the VBE. Just type the name of the procedure, including any arguments that may apply, and press Enter.

I discuss these methods of executing procedures in the following sections.

Note  

In many cases, a procedure will not work properly unless it is executed in the appropriate context. For example, if a procedure is designed to work with the active worksheet, it will fail if a chart sheet is active. A good procedure incorporates code that checks for the appropriate context and exits gracefully if it can't proceed.

Executing a procedure with the Run Sub/UserForm command

The VBE Run Sub/UserForm menu command is used primarily to test a procedure while you are developing it. You would never require a user to activate the VBE to execute a procedure. Choose Run image from book Run Sub/UserForm in the VBE to execute the current procedure (in other words, the procedure that contains the cursor). Or, press F5, or use the Run Sub/UserForm button on the Standard toolbar.

If the cursor is not located within a procedure when you issue the Run Sub/UserForm command, VBE displays its Macro dialog box so that you can select a procedure to execute.

Executing a procedure from the Macro dialog box

Choosing Excel's Developer image from book Code image from book Macros command displays the Macro dialog box, as shown in Figure 9-1. (You can also press Alt+F8 to access this dialog box.) Use the Macros In drop-down box to limit the scope of the macros displayed (for example, show only the macros in the active workbook).

image from book
Figure 9-1: The Macro dialog box.

The Macro dialog box does not display

  • Function procedures

  • Sub procedures declared with the Private keyword

  • Sub procedures that require one or more arguments

  • Sub procedures contained in add-ins

Tip  

Even though procedures stored in an add-in are not listed in the Macro dialog box, you still can execute such a procedure if you know the name. Simply type the procedure name in the Macro Name field in the Macro dialog box and then click Run.

Executing a procedure with a Ctrl+shortcut key combination

You can assign a Ctrl+shortcut key combination to any procedure that doesn't use any arguments. If you assign the Ctrl+U key combo to a procedure named UpdateCustomerList , for example, pressing Ctrl+U executes the that procedure.

When you begin recording a macro, the Record Macro dialog box gives you the opportunity to assign a shortcut key. However, you can assign a shortcut key at any time. To assign a Ctrl shortcut key to a procedure (or to change a procedure's shortcut key), follow these steps:

  1. Activate Excel and choose Developer image from book Code image from book Macros.

  2. Select the appropriate procedure from the list box in the Macro dialog box.

  3. Click the Options button to display the Macro Options dialog box (see Figure 9-2).

    image from book
    Figure 9-2: The Macro Options dialog box lets you assign a Ctrl key shortcut and an optional description to a procedure.

  4. Enter a character into the Ctrl+ text box.

    Note  

    The character that you enter into the Ctrl+ text box is case-sensitive. If you enter a lowercase s, the shortcut key combo is Ctrl+S. If you enter an uppercase S, the shortcut key combo is Ctrl+Shift+S.

  5. Enter a description (optional). If you enter a description for a macro, it is displayed at the bottom of the Macro dialog box when the procedure is selected in the list box.

  6. Click OK to close the Macro Options dialog box, and then click Close to close the Macro dialog box.

Caution  

If you assign one of Excel's predefined shortcut key combinations to a procedure, your key assignment takes precedence over the predefined key assignment. For example, Ctrl+S is the Excel predefined shortcut key for saving the active workbook. But if you assign Ctrl+S to a procedure, pressing Ctrl+S no longer saves the active workbook.

Tip  

The following keyboard keys are not used by Excel 2007 for Ctrl+key combinations: E, J, M, and Q. Excel doesn't use too many Ctrl+Shift+key combinations. In fact, you can use any of them except F, L, N, O, P, and W.

Executing a procedure from the Ribbon

If you're willing to go through a bit of effort, you can write XML code to add a new button (or other control) to the Ribbon and assign your macro to that control. Note that modifying the Ribbon is done outside of Excel, and it cannot be done using VBA.

CROSS-REFERENCE  

Refer to Chapter 22 for more information about customizing the Ribbon.

Executing a procedure from a customized shortcut menu

A macro can also be executed by clicking a menu item in a customized shortcut menu. A shortcut menu appears when you right-click an object or range in Excel.

CROSS-REFERENCE  

Refer to Chapter 23 for more information about customizing shortcut menus .

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() '   ... [code goes here] ...     UpdateSheet '   ... [code goes here] ... End Sub 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) '   ... [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, 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 image from book 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.

image from book
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 image from book 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.

image from book
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 CloseItDown 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.

image from book
 

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" 

Executing a procedure by clicking an object

Excel provides a variety of objects that you can place on a worksheet or chart sheet, and you can attach a macro to any of these objects. These objects fall into several classes:

  • ActiveX controls

  • Forms controls

  • Inserted objects (Shapes, SmartArt, WordArt, charts , and pictures)

Note  

The Developer image from book Controls image from book Insert drop-down list contains two types of controls that you can insert on a worksheet: Form controls and ActiveX controls. The ActiveX controls are similar to the controls that you use in a UserForm. The Forms controls were designed for Excel 5 and Excel 95, but they can still be used in later versions (and may be preferable in some cases).

Unlike the Form controls, the ActiveX controls cannot be used to execute an arbitrary macro. An ActiveX control executes a specially-named macro. For example, if you insert an ActiveX button control named CommandButton1, clicking the button executes a macro named CommandButton1_Click, which must be located in the code module for the sheet on which the control was inserted.

Refer to Chapter 13 for information about using controls on worksheets.

To assign a procedure to a Button object from the Form controls, follow these steps:

  1. Select Developer image from book Controls image from book Insert and click the button in the Form Controls group .

  2. Click the worksheet to create the button. Or, you can drag your mouse on the worksheet to change the default size of the button.

    Excel jumps right in and displays the Assign Macro dialog box (see Figure 9-4). It proposes a macro that's based on the button's name.

    image from book
    Figure 9-4: Assigning a macro to a button.

  3. Select or enter the macro that you want to assign to the button and then click OK.

You can always change the macro assignment by right-clicking the button and choosing Assign Macro.

To assign a macro to a Shape, SmartArt, WordArt, chart, or picture, right-click the object and choose Assign Macro from the shortcut menu.

Executing a procedure when an event occurs

You might want a procedure to execute when a particular event occurs. Examples of events include opening a workbook, entering data into a worksheet, saving a workbook, clicking a CommandButton ActiveX control, and many others. A procedure that is executed when an event occurs is an event handler procedure. Event handler procedures are characterized by the following:

  • They have special names that are made up of an object, an underscore , and the event name. For example, the procedure that is executed when a workbook is opened is Workbook_Open .

  • They are stored in the Code module for the particular object.

CROSS-REFERENCE  

Chapter 19 is devoted to event handler procedures.

Executing a procedure from the Immediate window

You also can execute a procedure by entering its name in the Immediate window of the VBE. If the Immediate window is not visible, press Ctrl+G. The Immediate window executes VBA statements while you enter them. To execute a procedure, simply enter the name of the procedure in the Immediate window and press Enter.

This method can be quite useful when you're developing a procedure because you can insert commands to display results in the Immediate window. The following procedure demonstrates this technique:

 Sub ChangeCase()     Dim MyString As String     MyString = "This is a test"     MyString = UCase(MyString)     Debug.Print MyString End Sub 

Figure 9-5 shows what happens when you enter ChangeCase in the Immediate window: The Debug.Print statement displays the result immediately.

image from book
Figure 9-5: Executing a procedure by entering its name in the Immediate window.



Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net