Procedures In Excel VBA

A procedure is a block of instructions grouped together to be executed as a logical unit. By clearly dividing tasks into procedures, your module’s code becomes more organized and easier to maintain.

You have already encountered both general procedures and event procedures.

Structure of a General Procedure:

Sub ProcedureName(ParameterList)
    [ InstructionBlock ]
    [ Exit Sub ]
    [ InstructionBlock ]
End Sub

• After the procedure name, a parameter list enclosed in parentheses can be given.
• If there are multiple parameters, they are separated by commas.
• The statement Exit Sub can be used to immediately exit the procedure based on a special condition, rather than waiting until the end of the procedure.
• When calling a procedure, parentheses around the parameter list are not used:

ProcedureName ParameterList

Example:

The procedure ShowMaximum() is called from two different places. It calculates the maximum of two passed parameters and displays the result:

Sub Procedure1()
    Dim a As Double, b As Double
    a = 4.5
    b = 7.2
    ShowMaximum a, b
End Sub

Sub Procedure2()
    Dim c As Double
    c = 23.9
    ShowMaximum c, 5.6
End Sub

Sub ShowMaximum(x As Double, y As Double)
    If x > y Then
        MsgBox "Maximum: " & x
    Else
        MsgBox "Maximum: " & y
    End If
End Sub

Explanation:

• ShowMaximum() expects two parameters of type Double: variables x and y.
• In Procedure1(), it is called with variables a and b.
• In Procedure2(), it is called with variable c and the constant 5.6.
• Both calls pass two numbers to ShowMaximum(), which uses a conditional statement to find and display the maximum.
• After finishing, control returns to the calling procedure.
• Variables or constants passed as arguments do not need to have the same names as parameters in the procedure.
• Procedures can be called repeatedly from any part of the program with different arguments.
• It is important that the number, order, and data types of parameters match.

Variable Scope Reminder:

• Local variables a and b exist only within Procedure1().
• Local variable c exists only within Procedure2().
• Parameters x and y exist only within ShowMaximum().
• Even if variable names repeat in different procedures, scope ensures there is no confusion or conflict.

Tips:

• Pressing Shift + F2 on a procedure or function name jumps to its definition.
• Pressing Ctrl + Shift + F2 returns to where you came from.
• Pressing Shift + F2 on a variable name jumps to its declaration.