VBA Course: Procedures and Functions
Public - Private
For now, all the procedures created are of type Public, they are accessible from all modules.
Sub example()
'Is equivalent to:
Public Sub example()
To make a procedure inaccessible outside of the module, add Private:
Private Sub example()
Calling a Procedure from Another Procedure
To execute a procedure from another procedure, simply enter its name.
A simple example:
Private Sub warning()
MsgBox "Warning!"
End Sub
Sub example()
If Range("A1") = "" Then
warning '<= executes the "warning" procedure
End If
End Sub
Here, when the example procedure is called and A1 equals "", the warning procedure is executed and displays the message box.
Arguments
Arguments allow you to pass values from one procedure to another (because, remember, variables are not accessible by default from other procedures).
Adding an argument text to the warning procedure:
Private Sub warning(text As String)
MsgBox "Warning: " & text & "!"
End Sub
Sub example()
If Range("A1") = "" Then 'If A1 is empty
warning "empty cell"
ElseIf Not IsNumeric(Range("A1")) Then 'If A1 is not numeric
warning "non-numeric value"
End If
End Sub
The argument added to the warning procedure is of type String:
Private Sub warning(text As String)
To execute the warning procedure, you need to provide an argument of type String:
warning "empty cell"
In case of multiple arguments, they should be separated by commas.
Optional Arguments
By default, if a procedure requires arguments, they are mandatory to execute the procedure.
However, optional arguments can be added after the mandatory arguments using Optional, for example:
Private Sub dialogBox(name As String, Optional firstName, Optional age)
This procedure can then be called with or without optional arguments, like this:
'Example 1: display only the name
dialogBox name
'Example 2: display the name and first name
dialogBox name, firstName
'Example 3: display the name and age
dialogBox name, , age
'Example 4: display the name, first name, and age
dialogBox name, firstName, age
The arguments must be provided in order.
To check if an optional argument is provided or not, we will use the IsMissing function. Since this function is only compatible with certain variable types (including Variant), the type of optional arguments has not been declared (undeclared type = Variant).
Here's an example using the two code sections above:
Sub example()
Dim name As String, firstName As String, age As Integer
name = Range("A1")
firstName = Range("B1")
age = Range("C1")
'Example 1: display only the name
dialogBox name
'Example 2: display the name and first name
dialogBox name, firstName
'Example 3: display the name and age
dialogBox name, , age
'Example 4: display the name, first name, and age
dialogBox name, firstName, age
End Sub
Private Sub dialogBox(name As String, Optional firstName, Optional age)
'If age is missing
If IsMissing(age) Then
If IsMissing(firstName) Then 'If the first name is missing, display only the name
MsgBox name
Else 'Otherwise, display the name and first name
MsgBox name & " " & firstName
End If
'If age is provided
Else
If IsMissing(firstName) Then 'If the first name is missing, display the name and age
MsgBox name & ", " & age & " years old"
Else 'Otherwise, display the name, first name, and age
MsgBox name & " " & firstName & ", " & age & " years old"
End If
End If
End Sub
Preview (with name, first name, and age):
It is also possible to provide default values to optional arguments and then test these values (instead of using the IsMissing function):
Private Sub dialogBox(name As String, Optional firstName As String = "", Optional age As Integer = 0)
'If age is missing
If age = 0 Then
If firstName = "" Then 'If the first name is missing, display only the name
MsgBox name
Else 'Otherwise, display the name and first name
MsgBox name & " " & firstName
End If
'If age is provided
Else
If firstName = "" Then 'If the first name is missing, display the name and age
MsgBox name & ", " & age & " years old"
Else 'Otherwise, display the name, first name, and age
MsgBox name & " " & firstName & ", " & age & " years old"
End If
End If
End Sub
ByRef - ByVal
By default, arguments are of type ByRef, which means that if a variable is passed as an argument, its reference is transmitted. In other words, if the variable is modified in the sub-procedure, it will also be modified in the calling procedure.
For example:
Sub example()
Dim number As Integer
number = 30
square number
MsgBox number
End Sub
Private Sub square(ByRef value As Integer) 'ByRef is not necessary (default type)
value = value ^ 2
End Sub
To better understand, here's what happens when the macro is executed:
'The initial value of the variable "number" is 30
number = 30
'The sub-procedure is called with the variable "number" as an argument
square number
'The variable "value" refers to the variable "number" passed as an argument, so if the "value" variable is modified, the "number" variable will also be modified
Private Sub square(ByRef value As Integer)
'The value of the "value" variable is modified (thus also modifying the "number" variable)
value = value ^ 2
'End of the sub-procedure
End Sub
'The "number" variable has been modified, and 900 is displayed in the message box
MsgBox number
The second option is to use ByVal.
Unlike ByRef, which directly references the variable, ByVal only passes its value, which means that the variable passed as an argument is not modified.
Here's what happens with the previous code and ByVal:
'The initial value of the variable "number" is 30
number = 30
'The sub-procedure is called with the variable "number" as an argument
square number
'The "value" variable copies the value of the "number" variable (the two variables are not linked)
Private Sub square(ByVal value As Integer)
'The value of the "value" variable is modified
value = value ^ 2
'End of the sub-procedure (in this example, the sub-procedure doesn't serve any purpose)
End Sub
'The "number" variable has not been modified, so 30 is displayed in the message box
MsgBox number
Functions
The main difference between Sub and Function is that a function returns a value.
Here's a simple example:
Function square(number As Double)
square = number ^ 2 'The "square" function returns the value of "square"
End Function
Sub example()
Dim result As Double
result = square(9.876) 'The "result" variable receives the value returned by the function
MsgBox result 'Display the result (in this case, the square of 9.876)
End Sub
You can notice that the arguments of a function are enclosed in (), unlike procedures where they are optional.