Procedures in VBA

A procedure holds a group of VBA statements that accomplishes a desired Task. A procedure is a series of VBA statements that resides in a VBA module, which you access in the Visual Basic Editor. 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).

Some procedures are written to receive arguments. An argument is simply information that is used by the procedure that is "passed" to the procedure when it is executed. Procedure arguments work much like the arguments 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

Now lets discuss this syntax a little bit in detail.

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.

Procedures are used a lot when Macros are written, mainly because they make the code re-usable. Suppose you have written a code a sort the numbers in ascending order. Now if you want to again use this code then you can just write the sorting code inside a procedure and call it from by passing the arguments (the list of numbers you want to sort).