Creating a Procedure

Adding a procedure to a module is a fairly simple task. The procedure that you create can be either a Sub procedure or a Function procedure. For now, it's sufficient to know that a Sub procedure is like a command on a menu: When called, it just does its job and doesn't return anything. A Function procedure, on the other hand, is more like a built-in function in that it returns a value. However, the steps for creating either type of procedure are the same:

1. In the VBA editor, choose InsertOProcedure.

The Add Procedure dialog box opens. All steps to follow take place in the Add Procedure dialog box.

2. Type in a name for your procedure.

The name must begin with a letter and cannot contain any blank spaces or punctuation marks. To create a practice procedure, enter a simple name like mySub.

3. Choose the type of procedure you want to create (Sub or Function).

For your first practice procedure, choose Sub. The remaining options you can ignore; the default settings are fine.

The Add Procedure dialog box closes. Your module contains a new procedure with the name that you provided in Step 2.

The two lines of VBA code needed to define the new procedure are typed into your module as soon as you click OK. The first line begins with Public Sub or Public Function, followed by the procedure name and a pair of closed parentheses. For example, if (in the Add Procedure dialog box) you create a Sub procedure named mySub, the following VBA lines are added to your module:

Public Sub mySub() End Sub

The Public keyword at the start of each procedure defines the scope of each procedure. By default, procedures in a standard module are public, meaning that they're visible to all objects in the current database. In a standard module, you can omit the Public keyword and just begin the line with the Sub or Function keyword. Either way, the module will be public (visible to all objects in the database).

In the module, the procedure name always ends in a pair of closed parentheses, as in mySub() or myFunc(). The parentheses are required, so they're typed in automatically when you click OK in the Add Procedure dialog box. Each procedure ends with an End Sub or End Function statement.

Figure 3-2 shows an example where I used the Add Procedure dialog box (twice) to create a Sub procedure named mySub and a Function procedure named myFunc. The module is visible in the VBA editor's Code window.

Figure 3-2:

Sub and Function procedures in a standard module.

\ VBA Practice - Modulel (Code)

(General) | myFunc

\ VBA Practice - Modulel (Code)

(General) | myFunc

Option Compare Database

Public Sub mySub () End Sub _

Public Function myFunc() End Function

~mySub() procedure

-myFunc() procedure Code window

Any code that you type into the procedure must be typed between the two lines that define the procedure. You can easily position the cursor within any procedure by clicking within that procedure. You can also move the cursor into a procedure just by choosing the procedure's name from the Procedure menu in the Code window.

0 0

Post a comment