A Quick Look at Procedures and Functions with Excel VBA

The main components of a VBA program are procedures and functions, which are fragments of code enclosed between the Sub and End Sub statements or between the Function and End Function statements.

For example, a procedure in VBA can be written as:

Sub <ProcedureName>(<Argument1>, <Argument2>, ... )
    <VBAStatement1>
    <VBAStatement2>
    ...
End Sub

Unlike a procedure, the name of a function also acts as a variable and is used to return a value to the point where the function was called. A function is generally written as follows:

Function <FunctionName>(<Argument1>, <Argument2>, ... )
    <VBAStatement1>
    <VBAStatement2>
    ...
    <FunctionName> = <ReturnValue>
End Function

Typically, a program consists of many procedures and functions, which can be placed in one or several modules. Modules are grouped into projects, and a single project can contain multiple different programs that use shared modules or procedures.

Each procedure within a single module must have a unique name; however, a project can contain several different modules. It is generally recommended to use only unique procedure names within a single project, although exceptions are allowed.

If a project contains several different procedures with the same name, the procedure name should be qualified when calling it, using the following syntax:

<ModuleName>.<ProcedureName>

If the module name consists of multiple words, it should be enclosed in square brackets. For example, if a module is called Mathematical Procedures and the procedure is Secans(), the call can look like this:

[Mathematical Procedures].Secans

It is also permissible to use procedures located in other projects. In this case, an additional level of qualification may be required:

<ProjectName>.<ModuleName>.<ProcedureName>

Note that to use written procedures or functions, they must be called. A procedure with a non-empty list of arguments can only be called from another procedure or function by using its name with the list of actual argument values as one of the VBA statements.

A function can be called not only through a separate VBA statement but also by placing its name with the list of actual argument values directly in a formula or expression in a VBA program or, for example, directly in the formula of an active Excel worksheet cell.

If the called procedure has a unique name and is in the same module as the calling procedure, it is sufficient to specify its name and provide the list of actual argument values without enclosing it in parentheses.

The second way to call a procedure is by using the Call statement. First comes the Call keyword, followed by the procedure name and the list of parameters; in this case, parentheses around the arguments are mandatory.

It must be enclosed in parentheses. Here are examples of calling a procedure named CRC() with two arguments (a constant and an expression):

CRC 7, i + 2

or

Call CRC(7, i + 2)

A function can be called similarly to a procedure; however, a more common way to call functions is to use its name with the list of parameters enclosed in parentheses on the right-hand side of an assignment statement. Here is an example of calling two functions — Left() and Mid() — and using their return values in an expression:

yStr = Left(y, 1) & Mid(y, 2, 1)

There are two different ways to pass variables to a procedure or function: by reference and by value.

• If a variable is passed by reference, the procedure or function receives the address of that variable in memory. This means the formal argument of the procedure is identified with the actual parameter passed to it. Therefore, the called procedure can modify the value of the actual parameter: if the formal argument is changed, the change affects the actual parameter passed in the call.
• If the actual parameter is passed by value, the formal argument of the called procedure or function receives only the value of the actual parameter, not the variable itself. Thus, any changes to the formal argument do not affect the value of the variable used as the actual parameter.

The method of passing parameters to a procedure or function is specified when declaring its arguments: the argument name may be preceded by an explicit specifier. ByRef indicates passing by reference, and ByVal indicates passing by value. If no explicit specifier is given, the default is by reference.

Let’s illustrate this with an example. Suppose we have the following descriptions of two procedures:

Sub Main()
    a = 10
    b = 20
    c = 30
    Call Example1(a, b, c)
    Call MsgBox(a)
    Call MsgBox(b)
    Call MsgBox(c)
End Sub

Sub Example1(x, ByVal y, ByRef z)
    x = x + 1
    y = y + 1
    z = z + 1
    Call MsgBox(x)
    Call MsgBox(y)
    Call MsgBox(z)
End Sub

The helper procedure Example1() uses three formal arguments, declared differently. Within this procedure, each of them is incremented by one, and their values are displayed using MsgBox().

The main procedure Main() sets the values of variables a, b, and c and then passes them as actual arguments to Example1(). In this case:

• The first argument is passed by reference (default),
• The second argument is passed by value,
• The third argument is passed by reference.

After returning from Example1(), the main procedure also displays the values of the three variables passed as arguments. In total, six values are displayed:

• 11, 21, 31 — all incremented by 1 and displayed by Example1(),
• 11, 20, 31 — displayed by Main(), where variables passed by reference were incremented, but the variable passed by value remained unchanged.