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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Option Explicit 'declare module-level integer variables Private int_Number1 As Integer, int_Number2 As Integer, int_Sum As Integer Private Sub sub_SumTwoIntegersNoArgs() 'private - ensures only procedures in this module can call this subroutine 'get the two integers from the User int_Number1 = Val(InputBox("Enter Number1:")) int_Number2 = Val(InputBox("Enter Number2:")) 'compute sum and display result in a Message Box int_Sum = int_Number1 + int_Number2 MsgBox "sub_SumTwoIntegersNoArgs = " & CStr(int_Sum), vbOKOnly End Sub Public Sub sub_RunSum() 'sum integers provided by User Call sub_SumTwoIntegersNoArgs End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
Option Explicit 'declare module-level integer variables Private int_Number1 As Integer, int_Number2 As Integer, int_Sum As Integer Public Sub sub_SumTwoIntegersArgs(Optional ByVal int_Num1 As Integer = 0, _ Optional ByRef int_Num2 As Integer = 0) 'public - lets procedures in this and other modules call this subroutine 'optional - lets you specify default values in case the argument is omitted ' when the subroutine is called 'ByVal - does not let you alter an argument's actual value, just a copy 'ByRef - lets you alter an argument's actual value 'monitor the effect of ByRef (VBA default) in argument declaration MsgBox "Number1 = " & CStr(int_Number1) & vbCrLf & "Number2 = " & _ CStr(int_Number2), vbOKOnly 'increment argument values int_Num1 = int_Num1 + 10 'actual value stays the same, copy increases int_Num2 = int_Num2 + 10 'actual value increases 'monitor the effect of ByRef (VBA default) in argument declaration MsgBox "Number1 = " & CStr(int_Number1) & vbCrLf & "Number2 = " & _ CStr(int_Number2), vbOKOnly 'compute sum and display result in a Message Box int_Sum = int_Num1 + int_Num2 MsgBox "sub_SumTwoIntegersArgs = " + CStr(int_Sum), vbOKOnly End Sub Public Sub sub_RunSum() 'get the two integers from the User int_Number1 = Val(InputBox("Enter Number1:")) int_Number2 = Val(InputBox("Enter Number2:")) 'sum integers provided by User Call sub_SumTwoIntegersArgs(int_Number1, int_Number2) End Sub |
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 (1st argument) and ByRef (2nd 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 1st argument’s value increases due to the ByVal keyword in its declaration (line #6). But the actual value in the memory address of the 2nd 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 1st 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 2nd argument of sub_SumTwoIntegersArgs (line #42). That’s because sub_SumTwoIntegersArgs declared its 2nd 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.