Procedures: Getting into the Details with Excel VBA

A procedure is a self-contained part of the code that has a name and can contain parameters, execute a sequence of instructions, and modify the values of its parameters.

[Private | Public | Friend] [Static] Sub <ProcedureName> [(<arguments>)]
    <instructions>
    [Exit Sub]
    <instructions>
End Sub

• Private — keyword indicating that the procedure is private, and its scope is limited to the module.
• Public — keyword indicating that the procedure is public and accessible from all other procedures in all modules.
• Friend — keyword used only in a class module, indicating that the procedure is friendly and its scope is the entire project.
• Static — keyword indicating that the local variables of the procedure retain their values between calls of this procedure.
• <ProcedureName> — the name of the procedure, which must follow the standard variable naming rules.
• <arguments> — a list of parameters whose values are passed into the procedure or returned from the procedure when it is called. Parameters are separated by commas.
• <instructions> — any group of instructions (usually a set of statements) executed within the Sub procedure.
• Exit Sub — a statement that causes an immediate exit from the procedure.

In the following examples, there are two procedures, DemoSub1 and DemoSub2, located in different modules. The DemoSub2 procedure is declared as private, so a call to DemoSub1 generates an error.

Procedure DemoSub1 does not see procedure DemoSub2 (Same standard module)

Sub DemoSub1()
    DemoSub2
End Sub

Procedure DemoSub1 does not see procedure DemoSub2 (Another standard module)

Private Sub DemoSub2()
    Beep
    MsgBox "Call received"
End Sub

To avoid this error, the DemoSub2 procedure must be declared either as public or without a scope specification.

Procedure DemoSub1 now sees procedure DemoSub2 (Another standard module)

Public Sub DemoSub2()
    Beep
    MsgBox "Call received"
End Sub