VBA-Lesson 9. Procedures and functions

Public - Private

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()

Starting a procedure from the middle of another procedure

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

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.

Optional arguments

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):

ByRef - ByVal

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 dialog

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...

Functions

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:

Articles on the topic: