The Sub Statement | VBA Declaration Statements

Introducing the Sub Statement

Sub procedures are procedures that perform one or more program actions but do not return a value. They (also called subroutines) are declared using the Sub statement.

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 Sub statement’s syntax. A Sub procedure declaration block begins with a Sub statement and ends with an End Sub statement. These statements encase the Sub 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 Sub Statement

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

• Any number of declaration and executable statements can make up a subroutine’s body. Of course, that’s possible only after declaring the Sub procedure. Also, you can’t declare another Sub, Function, or Property procedure inside a subroutine.
• Variables declared in a subroutine are local (in scope) to it. So too, for variables used in a subroutine but not declared anywhere inside or outside it.
• You can add either the Private or Public keyword to change the subroutine’s scope, but not both. Of course, you must always include the Sub keyword.
• Subroutines have project-level (i.e., public) scope by default. So, the Sub and Public keywords have the same effect. But adding the Public keyword improves code readability.
• Adding the Static keyword extends the lifetime of a subroutine’s local variables.

Passing Arguments with the Sub Statement

Sub 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 Sub 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 Sub Statement

There are several important things to note about argument declarations:

• The ByVal keyword passes an argument to a Sub procedure by value. Meaning the subroutine can only access a copy of the data held by the argument/variable. Since the subroutine 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 Sub procedure by reference. Meaning the subroutine can access the memory address of the argument/variable. Since the subroutine 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 Sub procedure, use the array name followed by empty parentheses.

The Sub Statement in Action

Sub Procedures without Arguments

The sample code below shows the Sub 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 subroutine, sub_SumTwoIntegersNoArgs, without arguments (lines #7 and #19).
• The subroutine 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 subroutine computes the sum and stores the result in int_Sum (line #16). Finally, it displays the result using the MsgBox function (line #17).

The image above shows the workings of the sub_SumTwoIntegersNoArgs subroutine.

A second Sub procedure, sub_RunSum, calls (i.e., invokes) sub_SumTwoIntegersNoArgs (line #25). sub_RunSum is declared without arguments (lines #22 and #27), and it:

• Transfers execution to sub_SumTwoIntegersNoArgs via the Call statement (line #25).
• Execution returns to the code line following the Call statement once sub_SumTwoIntegersNoArgs has run.

Sub Procedures with Arguments

The sample code below shows the Sub 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 subroutine, sub_SumTwoIntegersArgs, with two arguments (lines #6-7 and #33). 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.
• Displays the value of the variables passed to the subroutine (lines #18-19). These lines help monitor the before and after-effects of the ByVal and ByRef keywords.
• Increases the value of the arguments by 10 (lines #22-23). Only a copy of the 1^st argument’s value increases due to the ByVal keyword in its declaration (line #6). But the actual value in the memory address of the 2^nd argument increases due to the ByRef keyword in its declaration (line #7).
• Displays the value of the two variables passed into the subroutine (lines #26-27). 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.
• The subroutine computes the sum of argument values and stores the result in int_Sum (line #30). Finally, it displays the result using the MsgBox function (line #31).

The image above shows the workings of the sub_SumTwoIntegersArgs subroutine.

A second Sub procedure, sub_RunSum, calls (i.e., invokes) sub_SumTwoIntegersArgs (line #42). sub_RunSum is declared without arguments (lines #22 and #37), and it:

• Gets the two integers to be summed from the user via the InputBox function (lines #38-39). It stores the numbers in variables int_Number1 and int_Number2.
• Assigns the value of int_Number1 to the 1^st argument of sub_SumTwoIntegersArgs (line #42). That’s because sub_SumTwoIntegersArgs declared its 1st argument with a ByVal keyword (line #6).
• Assigns the memory address of int_Number2 to the 2^nd argument of sub_SumTwoIntegersArgs (line #42). That’s because sub_SumTwoIntegersArgs declared its 2^nd argument with a ByRef keyword (line #7).
• Transfers execution to sub_SumTwoIntegersArgs via the Call statement (line #42). Execution returns to the code line after the Call statement once sub_SumTwoIntegersArgs runs.