VBA-Leçon 9. Procédures et fonctions

Dans tout langage de programmation, il existe des concepts tels que des procédures et des fonctions. Ils ont également été inclus dans le langage de programmation intégré VBA pour Excel.

Procédures (Public - Private)

À ce stade, toutes les procédures que nous avons créées sont de type Public, ce qui signifie qu'elles sont accessibles depuis n'importe quel module.

 Sub example()
 
 'Identique à:
 Public Sub example()

Pour rendre la procédure disponible uniquement dans un certain module, le mot-clé Private est utilisé :

Private Sub example()

Exécuter une procédure au milieu d'une autre procédure

Pour exécuter une procédure depuis une autre procédure, il suffit de saisir son nom.

En voici un exemple très simple :

Private Sub warning()
    MsgBox "Caution !!!" '"AVERTISSEMENT!"
End Sub

Sub macro_test()
    If Range("A1") = "" Then
        warning '=> exécuter la procédure "warning"
    End If
    'etc...
End Sub

Arguments

Les arguments permettent d'utiliser les valeurs d'une procédure dans une sous-procédure (rappelons que par défaut, les variables ne sont disponibles que depuis la procédure dans laquelle elles ont été déclarées).

Private Sub warning(var_text As String)
    MsgBox "Caution : " & var_text & " !"
End Sub

Sub macro_test()
    If Range("A1") = "" Then
        warning "empty cell"               '"cellule vide"
    ElseIf Not IsNumeric(Range("A1")) Then
        warning "non-numerical value"      '"valeur non numérique"
    End If
End Sub

Un argument a été ajouté à la procédure "warning", dans ce cas il s'agit d'une variable "var_text" de type "String" (ruban) :

Private Sub warning(var_text As String)

Cette routine prend un argument, nous devons donc mettre une valeur après "warning" pour l'exécuter :

warning "empty cell"

Lorsque nous voulons écrire plusieurs arguments, ils doivent alors être séparés par des virgules.

Arguments facultatifs

Par défaut, si la procédure a des arguments, alors ils doivent être fournis, et s'ils ne le sont pas, alors la procédure ne sera pas exécutée.

Un argument facultatif peut être ajouté après un argument obligatoire, à l'aide du mot-clé Optionnel. Exemple:

Private Sub dialog_boxes(last_name As String, Optional first_name, Optional age)

Cette procédure peut désormais être exécutée avec ou sans argument optionnel, comme ici :

 'Exemple 1 : afficher le nom :
 dialog_boxes last_name1
   
 'Exemple 2 : on affiche le nom et le prénom :
 dialog_boxes last_name1, first_name1
   
 'Exemple 3 : afficher le nom et l'âge :
 dialog_boxes last_name1, , age1
   
 'Exemple 4 : afficher le nom, le prénom et l'âge :
 dialog_boxes last_name1, first_name1, age1

Les arguments doivent être saisis dans le bon ordre.

Pour tester si un argument facultatif est présent dans une procédure, nous utilisons la fonction IsMissing. Cette fonction n'est compatible qu'avec certains types de fonctions (de type Variant) et cela est critique car le type des arguments optionnels n'a pas été déclaré (type non déclaré = Variant).

Voici un exemple qui utilise les deux extraits de code évoqués ci-dessus :

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

    'Exemple 1 : afficher le nom :
    dialog_boxes last_name1
   
    'Exemple 2 : on affiche le nom et le prénom :
    dialog_boxes last_name1, first_name1
   
    'Exemple 3 : afficher le nom et l'âge :
    dialog_boxes last_name1, , age1
   
    'Exemple 4 : afficher le nom, le prénom et l'âge :
    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 'Si la variable âge est manquante...
       
        If IsMissing(first_name) Then 'Si la variable prénom_nom est manquante, alors
        							  'seul le nom de famille sera affiché
           MsgBox last_name
        Else 'Sinon, le nom et le prénom seront affichés
           MsgBox last_name & " " & first_name
        End If
       
    Else 'Si la variable âge est présente...

        If IsMissing(first_name) Then 'Si la variable prénom_nom est manquante, alors
                                      'le nom de famille et l'âge seront affichés
           MsgBox last_name & ", " & age & " years old"
        Else 'Dans le cas contraire, le nom, le prénom et l'âge seront affichés
           MsgBox last_name & " " & first_name & ", " & age & " years old"
        End If
   
    End If
       
End Sub

Voir image ci-dessous (exemple 1) :

ByRef - ByVal

Par défaut, les arguments sont de type ByRef, ce qui signifie que si une variable est passée en argument, une référence à celle-ci sera également transmise. En d’autres termes, si une variable a été modifiée par une autre sous-procédure, elle sera également modifiée dans la procédure externe qui appelle cette sous-procédure.

Exemple:

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 est facultatif
                                                      '(c'est la valeur par défaut)
    var_value = var_value * var_value
End Sub

Pour que ce soit clair, voici un exemple de ce qui se passera si l’exécution de la macro démarre :

 var_number = 30
 'La valeur initiale de la variable "var_number" est 30

 calcul_square var_number
 'La sous-procédure est exécutée avec "var_number" comme argument

 Private Sub calcul_square(ByRef var_value As Integer)
 'La variable "var_value" sert dans une certaine mesure à un accès rapide à la variable "var_number",
 'ce qui signifie que si la variable "var_value" est modifiée, la variable "var_number" sera également modifiée
 '(et ils ne doivent pas nécessairement avoir le même nom)
 var_value = var_value * var_value
 'La valeur de la variable "var_value" est modifiée (et donc "var_number" est également modifiée en même temps)

 End Sub
 'Fin de la sous-procédure

 MsgBox var_number
 'La variable "var_number" a été modifiée donc 900 sera désormais affiché dans la boîte de dialogue

La deuxième méthode consiste à utiliser ByVal.

Contrairement à ByRef, qui passe une référence (libellé), ByVal passe une valeur, ce qui signifie que la valeur passée en argument n'a pas été modifiée.

Ci-dessous, vous pouvez voir comment fonctionnent le code précédent et ByVal :

 var_number = 30
 'La valeur initiale de la variable "var_number" est 30

 calcul_square var_number
 'La sous-procédure est exécutée avec "var_number" comme argument

 Private Sub calcul_square(ByVal var_value As Integer)
 'La variable "var_value" copie la valeur de la variable "var_number" (les 2 variables ne sont pas liées)

 var_value = var_value * var_value
 'La valeur de la variable "var_value" a changé

 End Sub
 'Fin de la sous-procédure (dans cet exemple la sous-procédure n'a aucun effet)

 MsgBox var_number
 'La variable "var_number" n'a pas été modifiée et donc 30 sera affiché dans la boîte de dialogue

Ce que vous devez retenir : utilisez ByVal lorsque la variable ne doit pas être modifiée.

Les fonctions

La principale différence entre une procédure et une fonction est qu'une fonction renvoie une valeur.

Voici un exemple simple :

Function square(var_number)
    square = var_number ^ 2 'La fonction "carré" renvoie la valeur "racine carrée"
End Function

Sub macro_test()
    Dim result As Double
    result = square(9.876) 'La variable de résultat reçoit la valeur calculée par la fonction
    MsgBox result 'Le résultat s'affiche (dans ce cas le carré de 9,876)
End Sub

La fonction peut être utilisée sur une feuille de calcul comme n'importe quelle autre fonction dans Excel.

Par exemple, pour obtenir le carré de la valeur saisie dans la cellule A1 :