TOP

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

YouLibreCalc for Excel logo

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.

  • Une fonction est une routine qui renvoie un résultat. Un appel de fonction est une expression et peut être utilisé dans d'autres expressions ou sur le côté droit d'une instruction d'affectation (notée dans VBA par "Function") ;
  • Une procédure est toute routine qui n’est pas une fonction. Marqué dans VBA comme "Sub" (du mot "sous-programme").

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