About Procedures

A procedure is a series of VBA statements that resides in a VBA module, which you access in the Visual Basic Editor (VBE). A module can hold any number of procedures.

You have a number of ways to call, or execute, procedures. A procedure is executed from beginning to end, but it can also be ended prematurely.

Tip  

A procedure can be any length, but many people prefer to avoid creating extremely long procedures that perform many different operations. You may find it easier to write several smaller procedures, each with a single purpose. Then, design a main procedure that calls those other procedures. This approach can make your code easier to maintain.

Some procedures are written to receive arguments. An argument is simply information that is used by the procedure and that is passed to the procedure when it is executed. Procedure arguments work much like the arguments that you use in Excel worksheet functions. Instructions within the procedure generally perform logical operations on these arguments, and the results of the procedure are usually based on those arguments.

Declaring a Sub procedure

A procedure declared with the Sub keyword must adhere to the following syntax:

 [Private  Public][Static] Sub  name  ([  arglist  ])     [  instructions  ] [Exit Sub]     [  instructions  ] End Sub 

• Private : (Optional) Indicates that the procedure is accessible only to other procedures in the same module.

• Public : (Optional) Indicates that the procedure is accessible to all other procedures in all other modules in the workbook. If used in a module that contains an Option Private Module statement, the procedure is not available outside the project.

• Static : (Optional) Indicates that the procedure's variables are preserved when the procedure ends.

• Sub : (Required) The keyword that indicates the beginning of a procedure.

• name : (Required) Any valid procedure name.

• arglist : (Optional) Represents a list of variables, enclosed in parentheses, that receive arguments passed to the procedure. Use a comma to separate arguments. If the procedure uses no arguments, a set of empty parentheses is required.

• instructions : (Optional) Represents valid VBA instructions.

• Exit Sub : (Optional) A statement that forces an immediate exit from the procedure prior to its formal completion.

• End Sub : (Required) Indicates the end of the procedure.

Naming Procedures

Every procedure must have a name. The rules governing procedure names are generally the same as for variable names . Ideally, a procedure's name should describe what its contained processes do. A good rule is to use a name that includes a verb and a noun (for example, ProcessDate , PrintReport , Sort_Array , or CheckFilename ). Avoid meaningless names such as DoIt , Update , and Fix .

Some programmers use sentence -like names that describe the procedure (for example, WriteReportToTextFile and Get_Print_Options_ and_Print_Report ).

 

Note  

With a few exceptions, all VBA instructions in a module must be contained within procedures. Exceptions include module-level variable declarations, user -defined data type definitions, and a few other instructions that specify module-level options (for example, Option Explicit ).

Scoping a procedure

In the preceding chapter, I note that a variable's scope determines the modules and procedures in which the variable can be used. Similarly, a procedure's scope determines which other procedures can call it.

PUBLIC PROCEDURES

By default, procedures are public - that is, they can be called by other procedures in any module in the workbook. It's not necessary to use the Public keyword, but programmers often include it for clarity. The following two procedures are both public:

 Sub First() '    ... [code goes here] ... End Sub Public Sub Second() '    ... [code goes here] ... End Sub 

PRIVATE PROCEDURES

Private procedures can be called by other procedures in the same module but not by procedures in other modules.

Note  

When a user displays the Macro dialog box, Excel shows only the public procedures. Therefore, if you have procedures that are designed to be called only by other procedures in the same module, you should make sure that those procedures are declared as Private . Doing so prevents the user from running these procedures from the Macro dialog box.

The following example declares a private procedure named MySub :

 Private Sub MySub() '    ... [code goes here] ... End Sub 

Tip  

You can force all procedures in a module to be private - even those declared with the Public keyword - by including the following statement before your first Sub statement:

 Option Private Module 

If you write this statement in a module, you can omit the Private keyword from your Sub declarations.

Excel's macro recorder normally creates new Sub procedures called Macro1 , Macro2 , and so on. These procedures are all public procedures, and they will never use any arguments.