The Function Statement | VBA Declaration Statements

Introducing the Function Statement

Function procedures are procedures that perform one or more program actions and also return a value. ) They (also called Functions) are declared using the Function statement. The Function statement’s syntax is illustrated in the header image above.

Declaration statement name program identifiers and assign them memory (in RAM). Also, they may specify an identifier’s data type, initial value, scope, and lifetime. Moreover, they become definitions when they assign an initial value to an identifier.

The header image above illustrates the Function statement’s syntax. A Function procedure declaration block begins with a Function statement and ends with an End Function statement. These statements encase the Function procedure’s intended tasks (i.e., one or more code-lines).

In fact, all executable code in VBA must be written in procedures. So, Sub, Function, and Property procedures are very important VBA program identifiers.

Salient Points on the Function Statement

There are several vital things to note about the Function statement:

• Any number of declaration and executable statements can make up a function’s body. Of course, that’s possible only after declaring the Function procedure. Also, you can’t declare another Sub, Function, or Property procedure inside a function.
• Variables declared in a function are local (in scope) to it. So too, for variables used in a function but not declared anywhere inside or outside it.
• Expression, in the syntax, is the function’s return value. Its value must be of the data type specified in the Function statement’s declaration. Also, the return value must be assigned to the function’s name in the body of the function.
• Because functions return a value of a specified type, their name (and argument list) can be placed on the right-hand side of an assignment statement. In that case, their return value is assigned to the program identifier on the left side of the statement.
• You can add either the Private or Public keyword to change the function’s scope, but not both. Of course, you must always include the Function keyword.
• Functions have project-level (i.e., public) scope by default. So, the Function and Public keywords have the same effect. But adding the Public keyword improves code readability.
• Adding the Static keyword extends the lifetime of a function’s local variables.

Passing Arguments with the Function Statement

Function procedures may need input data, supplied upfront, to complete their tasks. In that case, their declarations include an argument list, as shown in the syntax (header image above).

The argument list is a comma-separated list of variables. Each variable (i.e., argument) holds data to be fed into the Function procedure. Moreover, you must declare each argument in the list according to the syntax shown in the image below.

Salient Points on Passing Arguments with the Function Statement

There are several important things to note about argument declarations:

• The ByVal keyword passes an argument to a Function procedure by value. Meaning the function can only access a copy of the data held by the argument/variable. Since the function can’t access the variable’s memory address, it can’t alter the data there. So, ByVal is useful when you don’t intend to change an argument’s actual data.
• The ByRef keyword passes an argument to a Function procedure by reference. Meaning the function can access the memory address of the argument/variable. Since the function can access the variable’s memory address, it can alter the data there. So, ByRef (which is VBA’s default setting) is useful when you intend to change an argument’s actual data.
• To pass an entire array to a Function procedure, use the array name followed by empty parentheses.

The Function Statement in Action

Function Procedures without Arguments

The sample code below shows the Function statement in use without arguments. Also, it demonstrates the crucial points discussed earlier.

In brief, the program does the following:

• Declares three integer variables, int_Number1, int_Number2, and int_Sum in code window line #4. These will store the numbers to add and the result obtained, respectively. Also, only code in this module can access these variables because of the Private keyword.
• Declares a function, fun_SumTwoIntegersNoArgs, without arguments (lines #7 and #18).
• The function gets the two integers to be summed from the user via the InputBox function (lines #12-13). It stores the numbers in variables int_Number1 and int_Number2.
• The function computes the sum and assigns the result (i.e., return value) to an identically named variable (line #16).

The image above shows the workings of the fun_SumTwoIntegersNoArgs function.

A second procedure, subroutine sub_RunSum, invokes the function by assigning its identically named variable to int_Sum (line #24). sub_RunSum is declared without arguments (lines #21and #27), and it:

• Transfers execution to fun_SumTwoIntegersNoArgs via assignment (line #24). Execution returns to the code line following the assignment once fun_SumTwoIntegersNoArgs has run.
• Displays the result using the MsgBox function (line #25).

Function Procedures with Arguments

The sample code below shows the Function statement in use with arguments. Also, it demonstrates the crucial points discussed earlier.

In brief, the program does the following:

• Declares three integer variables, int_Number1, int_Number2, and int_Sum (line #4). These will store the numbers to add and the resulting sum, respectively. Also, code both in and out of this module can access these variables due to the Public keyword.
• Declares a function, fun_SumTwoIntegersArgs, with two arguments (lines #7-8 and #24). The Optional keyword in the argument declaration means they can be omitted. If that occurs, then ‘0’ is adopted by default. Also, ByVal (1^st argument) and ByRef (2^nd argument) keywords act as described earlier.
• Increases the value of the arguments by 10 (lines #18-19). Only a copy of the 1^st argument’s value increases due to the ByVal keyword in its declaration (line #7). But the actual value in the memory address of the 2^nd argument increases due to the ByRef keyword in its declaration (line #8).
• The function computes the sum of argument values and assigns the result (i.e., return value) to an identically named variable (line #22).

The image above shows the workings of the fun_SumTwoIntegersArgs function.

A second procedure, subroutine sub_RunSum, invokes the function by assigning its identically named variable to int_Sum (line #38). sub_RunSum is declared without arguments (lines #27and #47), and it:

• Gets the two integers to be summed from the user via the InputBox function (lines #30-31). It stores the numbers in variables int_Number1 and int_Number2.
• Displays the value of these variables to help monitor the before and after-effects of the ByVal and ByRef keywords (lines #34-35).
• Transfers execution to fun_SumTwoIntegersArgs via assignment (line #38). Execution returns to the code line following the assignment once fun_SumTwoIntegersNoArgs has run.
• Assigns only the value of int_Number1 to the 1^st argument of fun_SumTwoIntegersArgs (line #38). That’s because fun_SumTwoIntegersArgs declared its 1^st argument with a ByVal keyword (line #7).
• Assigns the memory address of int_Number2 to the 2^nd argument of sub_SumTwoIntegersArgs (line #38). That’s because sub_SumTwoIntegersArgs declared its 2^nd argument with a ByRef keyword (line #8).
• Displays the value of the two variables passed into the function (lines #41-42) when fun_SumTwoIntegersArgs ends. So, the user can now see the effect of the ByVal and ByRef keywords. int_Number1‘s value remains the same whereas int_Number2’s value has increased by 10.
• Displays the result (from line #38) using the MsgBox function (line #45).