Public - Private:
At the moment, Every procedures which we have created are of the Public type, it means that they are accessible from any module.Sub Test()
'Similar to :
Public Sub Test()
To make a procedure accessible only from within the module, use Private :
Private Sub Test()
Launch a procedure from within a procedure:
To execute a procedure from a another procedure, just enter its name as demonstrated in below example:Private Sub Test()
MsgBox "This is a Test"
End Sub
Sub Test()
If Range("A1") = "" Then
Validate 'execute the procedure "Validate"
End If
'Execute if condition is true
End Sub
Here, when "Test" is executed and when A1 has the value "", then "Validate" procedure will be executed.
Arguments:
Arguments enables to use values from a procedure in a sub procedure (remember that by default, variables are only accessible from the procedure in which they are declared).Private Sub Validate(check1 As String)
MsgBox "Alert messege: " & check1 & " !"
End Sub
Sub Test()
If Range("A1") < 100 Then
Validate "number is less than 100"
ElseIf Not IsNumeric(Range("A1")) Then
Validate "non-numerical value"
End If
End Sub
An argument has been added to the "Validate" procedure, in this case it is the "check1" variable of type "String"
:
Private Sub Validate(check1 As String)
This procedure needs an argument, so we will have to put a value after "Validate" to execute it :
Validate "number is less than 100"
When there are multiple arguments, these should be separated by commas.
Optional arguments:
By default, if a procedure has arguments, which are mandatory, and if they are not included, the procedure will not execute.Optional arguments can be added after the mandatory ones using Optional, for example :
Private Sub Alert_box(Var1 As String, Optional Var2, Optional Var3)
Now this procedure can be executed with or without optional arguments, like this :
'Ex. 1 : the last name is displayed :
Alert_box Var1
'Ex. 2 : Var1 and Var2 are displayed :
Alert_box Var1, Var2
'Ex. 3 : Var1 and Var3 are displayed :
Alert_box Var1, Var3
'Ex. 4 : Var1, Var2 and Var3 are displayed :
Alert_box Var1, Var2, Var3
To check whether an optional argument is present, we will use the IsMissing function. This function is only compatible with certain types of functions (thus Variant), and this is vital because the type of the optional arguments has not been specified in the declaration (a non declared type is = Variant).
Here is an example using the two pieces of code above :
Sub Test()
Dim Var1 As String, Var2 As String, Var3 As Integer
Var1 = Range("A1")
Var2 = Range("B1")
Var3 = Range("C1")
'Ex. 1 : the last name is displayed :
Alert_box Var1
'Ex. 2 : Var1 and Var2 are displayed :
Alert_box Var1, Var2
'Ex. 3 : Var1 and Var3 are displayed :
Alert_box Var1, Var3
'Ex. 4 : Var1, Var2 and Var3 are displayed :
Alert_box Var1, Var2, Var3
End Sub
Private Sub Alert_box(Var1 As String, Optional Var2, Optional Var3)
If IsMissing(Var3) Then 'If the Var3 variable is missing ...
If IsMissing(Var1 ) Then 'If the Var1 variable is missing, only the Var2 will be displayed
MsgBox Var2
Else 'Otherwise, Var2 and Var1 will be displayed
MsgBox Var2 & " " & Var1
End If
Else 'If the Var3 variable is present ...
If IsMissing(Var1 ) Then 'If the Var1 variable is missing,Var2 and Var3 will be displayed
MsgBox Var2 & ", " & Var3 & " Checked"
Else 'Otherwise, Var2, Var1 , and Var3 will be displayed
MsgBox Var2 & " " & Var1 & ", " & Var3 & " Checked"
End If
End If
End Sub
ByRef - ByVal:
By default, arguments are of the ByRef type, which means that if a variable is submitted as an argument, its reference will be transmitted. In other words, if the variable is modified in the sub procedure, it will also be modified in the procedure that called the sub procedure.Eg.
Sub 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 does not need to be specified (because it is the default)
var_value = var_value * var_value
End Sub
To make this even clearer, here is an explanation of what happens when the macro is launched :
var_number = 30
'The initial value of the "var_number" variable is 30
calcul_square var_number
'The sub procedure is launched with "var_number" as an argument
Private Sub calcul_square(ByRef var_value As Integer)
'The "var_value" variable is in some way a shortcut to "var_number", which means that if the "var_value" variable is modified, the "var_number" variable will also be modified (and they don't have to have the same name)
var_value = var_value * var_value
'The value of the "var_value" variable is modified (and therefore the "var_number" is modified as well)
End Sub
'End of sub procedure
MsgBox var_number
'The "var_number" variable was modified, so 900 will now be displayed in the dialog box
A second method is to use ByVal.
Unlike ByRef, which transmits the reference (shortcut), ByVal transmits the value, which means that the value submitted as an argument will not be modified.
Here you can see how the code immediately above, and ByVal work :
var_number = 30
'The initial value of the variable "var_number" is 30
calcul_square var_number
'The sub procedure is launched with the variable "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 linked)
var_value = var_value * var_value
'The value of the variable "var_value" is modified
End Sub
'End of sub procedure (the sub procedure in this example doesn't have any effect at all)
MsgBox var_number
'The variable "var_number" has not been modified, and so 30 will be displayed in the dialog box
What you should remember : using ByVal when a variable shouldn't be modified ...
Functions:
The main difference between Sub and Function is the value returned by the function.Here is a straightforward example :
Function square(var_number)
square = var_number ^ 2 'The function "square" returns the value of "square"
End Function
Sub Test()
Dim result As Double
result = square(9.876) 'The variable result is assigned the value returned by the function
MsgBox result 'Displays the result (the square of 9.876, in this case)
End Sub
Functions can be used on a worksheet like any other Excel function.
For example, to obtain the square of the value of A1 :
No comments:
Post a Comment