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
test procedures functions

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

test2 procedures functions

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.