At this point, all the procedures we've created are of type Public, which means they're accessible from any module.
Sub example() 'Identical to: Public Sub example()
To make the procedure available only in a certain module, the keyword Private is used:
Private Sub example()
To execute a procedure from within another procedure, simply enter its name.
Here is a very simple example of this:
Private Sub warning()
MsgBox "Caution !!!"
End Sub
Sub macro_test()
If Range("A1") = "" Then
warning ' <= execute procedure "warning"
End If
'etc...
End Sub
Arguments make it possible to use values from a procedure in a sub-procedure (remember that by default, variables are only available from the procedure in which they were declared).
Private Sub warning(var_text As String)
MsgBox "Caution : " & var_text & " !"
End Sub
Sub macro_test()
If Range("A1") = "" Then
warning "empty cell"
ElseIf Not IsNumeric(Range("A1")) Then
warning "non-numeric value"
End If
End Sub
An argument was added to the "warning" procedure, in this case it is a "var_text" variable of type "String":
Private Sub warning(var_text As String)
This routine takes an argument, so we have to put a value after "warning" to execute it:
warning "empty cell"
When we want to write several arguments, then they should be separated by commas.
By default, if a procedure has arguments, they must be supplied, and if they are not supplied, the procedure will not be executed.
An optional argument can be added after a mandatory one, using the Optional keyword. For example:
Private Sub dialog_boxes(last_name As String, Optional first_name, Optional age)
This procedure can now be executed with or without an optional argument, as here:
Example 1: display the last name: dialog_boxes last_name1 "Example 2: we display the surname and first name: dialog_boxes last_name1, first_name1 "Example 3: we display the last name and age: dialog_boxes last_name1, , age1 Example 4: display the last name, first name and age: dialog_boxes last_name1, first_name1, age1
Arguments must be entered in the correct order.
To test if an optional argument is present in a procedure, we use the IsMissing function. This function is only compatible with some function types (of type Variant) and this is critical because the type of the optional arguments was not declared (undeclared type = Variant).
Here is an example that uses the two code snippets discussed above:
Sub macro_test()
Dim last_name1 As String, first_name1 As String, age1 As Integer
last_name1 = Range("A1")
first_name1 = Range("B1")
age1 = Range("C1")
Example 1: display the last name:
dialog_boxes last_name1
"Example 2: we display the surname and first name:
dialog_boxes last_name1, first_name1
"Example 3: we display the last name and age:
dialog_boxes last_name1, , age1
Example 4: display the last name, first name and age:
dialog_boxes last_name1, first_name1, age1
End Sub
Private Sub dialog_boxes(last_name As String, Optional first_name, Optional age)
If IsMissing(age) Then 'If the variable age is missing...
If IsMissing(first_name) Then 'If the variable first_name is missing, then
'only the last name will be displayed
MsgBox last_name
Else 'Otherwise, the last name and first name will be displayed
MsgBox last_name & " " & first_name
End If
Else 'If the variable age is present...
If IsMissing(first_name) Then 'If the variable first_name is missing, then
'surname and age will be displayed
MsgBox last_name & ", " & age & " years"
Else 'Otherwise, last name, first name and age will be displayed
MsgBox last_name & " " & first_name & ", " & age & " years"
End If
End If
End Sub
See picture below (example 1):
By default, arguments are of type ByRef, which means that if a variable is passed as an argument, a reference to it will also be passed. In other words, if a variable has been changed by another sub-procedure, it will also be changed in the outer procedure that calls that sub-procedure.
For example:
Sub macro_test()
Dim var_number As Integer
var_number = 30
calcul_square var_number
MsgBox var_number
End Sub
Private Sub calcul_square(ByRef var_value As Integer) 'ByRef is optional
'(is the default value)
var_value = var_value * var_value
End Sub
To make it clear, below is an example of what will happen if the macro is started to execute:
var_number = 30 'The initial value of the variable "var_number" is 30 calcul_square var_number 'The sub-procedure is run with "var_number" as an argument Private Sub calcul_square(ByRef var_value As Integer) 'The variable "var_value" serves to some extent for quick access to the variable "var_number", 'which means that if the variable "var_value" is changed, the variable "var_number" will also be changed '(and they don't have to have the same name) var_value = var_value * var_value 'The value of the variable "var_value" has been changed (and therefore "var_number" has also been changed at the same time) End Sub 'End of sub-procedure MsgBox var_number 'Variable "var_number" has been changed so 900 will now be displayed in the dialogThe second method is to use ByVal.
Unlike ByRef, which passes a reference (label), ByVal passes a value, which means that the value passed as an argument has not been modified.
Below you can see how the previous code and ByVal work:
var_number = 30 'The initial value of the variable "var_number" is 30 calcul_square var_number 'The sub-procedure is run with "var_number" as an argument Private Sub calcul_square(ByVal var_value As Integer) 'The variable "var_value" copies the value of the variable "var_number" (the 2 variables are not related) var_value = var_value * var_value 'The value of the variable "var_value" has been changed End Sub 'End of sub-procedure (in this example the sub-procedure has no effect on anything) MsgBox var_number 'The variable "var_number" has not been changed, so 30 will be displayed in the dialog
What you need to remember: Use ByVal when the variable should not be changed...
The main difference between a procedure and a function is that a function returns a value.
Here is a simple example:
Function square(var_number)
square = var_number ^ 2 'The "square" function returns the value "square root"
End Function
Sub macro_test()
Dim result As Double
result = square(9.876) 'The variable result is assigned the value that was calculated by the function
MsgBox result 'The result is displayed (in this case, the square for 9.876)
End Sub
A function can be used on a worksheet just like any other function in Excel.
For example, to get the square of the value entered in cell A1:
