Fonction VBA – Appel, Valeur de Retour et Paramètres

Ce tutoriel vous apprendra à créer et à utiliser des fonctions avec et sans paramètres en VBA

VBA contient un grand nombre de fonctions intégrées que vous pouvez utiliser, mais vous pouvez également écrire vos propres fonctions. Lorsque vous écrivez du code en VBA, vous pouvez le faire dans une procédure secondaire ou une fonction. Une fonction est capable de renvoyer une valeur à votre code. C’est extrêmement utile si vous voulez que VBA exécute une tâche et renvoie un résultat. Les fonctions VBA peuvent également être appelées depuis Excel, tout comme les fonctions Excel intégrées.

Création d’une Fonction sans Arguments

Pour créer une fonction, vous devez la définir en lui donnant un nom. La fonction peut ensuite être définie comme un type de données indiquant le type de données que vous souhaitez que la fonction renvoie.

Vous pouvez créer une fonction qui renvoie une valeur statique à chaque fois qu’elle est appelée, un peu comme une constante.

Function ObtenirValeur() As Integer
   ObtenirValeur = 50
End Function

Si vous exécutez la fonction, elle renverra toujours la valeur 50.

Vous pouvez également créer des fonctions qui font référence à des objets en VBA, mais vous devez utiliser le mot-clé Set pour renvoyer la valeur de la fonction.

Function ObtenirPlage() as Range
  Set ObtenirPlage = Range("A1:G4")
End Function

Si vous utilisez la fonction ci-dessus dans votre code VBA, la fonction renverra toujours la plage de cellules A1 à G4, quelle que soit la feuille sur laquelle vous travaillez.

Appel d’une Fonction à Partir d’une Sous-Procédure

Une fois que vous avez créé une fonction, vous pouvez l’appeler de n’importe où dans votre code en utilisant une sous-procédure pour appeler la fonction.

La valeur de 50 sera toujours renvoyée.

Vous pouvez également appeler la fonction ObtenirPlage à partir d’une sous-procédure.

Dans l’exemple ci-dessus, la fonction ObtenirPlage est appelée par la sous-procédure pour mettre en gras les cellules de l’objet Range.

Création de Fonctions

Argument Unique

Vous pouvez également attribuer un ou plusieurs paramètres à votre fonction. Ces paramètres peuvent être appelés des arguments.

Function ConvertirKiloEnLivres(dblKilo As Double) As Double
    ' Converti les kilogrammes en livres
    ConvertirKiloEnLivres = dblKilo * 2.2
End Function

Nous pouvons ensuite appeler la fonction ci-dessus à partir d’une sous-procédure afin de calculer le nombre de livres que représente une quantité spécifique de kilos.

Une fonction peut être appelée à partir de plusieurs procédures dans votre code VBA si nécessaire. C’est très utile car cela vous évite de devoir écrire le même code encore et encore. Elle vous permet également de diviser les longues procédures en petites fonctions faciles à gérer.

Dans l’exemple ci-dessus, nous avons 2 procédures – chacune d’entre elles utilise la fonction pour calculer la valeur en livres des kilos qui leur sont transmis dans l’argument dblKilo de la fonction.

Arguments Multiples

Vous pouvez créer une fonction avec plusieurs arguments et transmettre les valeurs à la fonction par le biais d’une sous-procédure.

Function CalculerÉcartJours(Date1 As Date, Date2 As Date) As Double
    CalculerÉcartJours = Date2 - Date1
End Function

Nous pouvons ensuite appeler la fonction pour calculer le nombre de jours entre 2 dates.

Arguments Facultatifs

Vous pouvez également transmettre des arguments facultatifs à une fonction. En d’autres termes, vous pouvez parfois avoir besoin de l’argument, et parfois non – en fonction du code avec lequel vous utilisez la fonction.

Function CalculerÉcartJours(Date1 As Date, Optional Date2 As Date) As Double
    ' Vérifie si la deuxième date a été spécifiée, sinon défini égal à aujourd'hui
    If Date2 = 0 Then Date2 = Date
    ' Calcule l'écart en jours entre les deux dates
    CalculerÉcartJours = Date2 - Date1
End Function

Valeur par Défaut des Arguments

Vous pouvez également définir la valeur par défaut des arguments facultatifs lorsque vous créez la fonction. Ainsi, si l’utilisateur omet l’argument, la valeur que vous avez définie par défaut sera utilisée à la place.

Function CalculerÉcartJours(Date1 As Date, Optional Date2 As Date = "2020-06-02") As Double
    ' Calcule l'écart en jours entre les deux dates
    CalculerÉcartJours = Date2 - Date1
End Function

ByVal et ByRef

Lorsque vous transmettez des valeurs à une fonction, vous pouvez utiliser les mots-clés ByVal ou ByRef. Si vous omettez l’un ou l’autre, le ByRef est utilisé par défaut.

ByVal signifie que vous transmettez une copie de la variable à la fonction, tandis que ByRef signifie que vous faites référence à la valeur originale de la variable. Lorsque vous transmettez une copie de la variable (ByVal), la valeur originale de la variable n’est PAS modifiée, mais lorsque vous faites référence à la variable, la valeur originale de la variable est modifiée par la fonction.

Function ObtenirValeur(ByRef intA As Integer) As Integer
    intA -intA * 4
    ObtenirValeur = intA
End Function

Dans la fonction ci-dessus, le ByRef pourrait être omis et la fonction fonctionnerait de la même manière.

Function ObtenirValeur(intA As Integer) As Integer
    intA -intA * 4
    ObtenirValeur = intA
End Function

Pour appeler cette fonction, nous pouvons exécuter une sous-procédure.

Sub TestValeurs()
    Dim intVal As Integer
    ' Initialisation de la variable avec la valeur 10
    intVal = 10
    ' Appel de la fonction ObtenirValeur et affiche le résultat dans la fenêtre d'exécution
    Debug.Print ObtenirValeur(intVal)
    ' Affiche la valeur de la variable intVal dans la fenêtre d'exécution
    Debug.Print intVal
End Sub

Notez que les fenêtres de débogage affichent la valeur 40 à chaque fois. Lorsque vous passez la variable IntVal à la fonction, la valeur 10 est transmise à la fonction et multipliée par 4. L’utilisation du mot-clé ByRef (ou son omission pure et simple) modifiera la valeur de la variable IntVal. Cela se voit lorsque vous montrez d’abord le résultat de la fonction dans la fenêtre immédiate (40), puis la valeur de la variable IntVal dans la fenêtre de débogage (également 40).

Si nous ne voulons PAS changer la valeur de la variable originale, nous devons utiliser ByVal dans la fonction.

Function ObtenirValeur(ByVal intA As Integer) As Integer
    intA = intA * 4
    ObtenirValeur = intA
End Function

Maintenant, si nous appelons la fonction depuis une sous-procédure, la valeur de la variable IntVal restera à 10.

Quitter une Fonction

Si vous créez une fonction qui teste une certaine condition et qu’une fois la condition vérifiée, vous voulez renvoyer la valeur de la fonction, vous devrez peut-être ajouter une instruction Exit Function dans votre code afin de quitter la fonction avant d’avoir exécuté tout le code de cette fonction.

Function TrouverChiffre(strRecherche As String) As Integer
   Dim i As Integer
    'Boucle sur chaque lettre de la chaîne
    For i = 1 To Len(strRecherche)
    'Si la lettre est numérique, renvoyez la valeur à la fonction
        If IsNumeric(Mid(strRecherche, i, 1)) Then
            TrouverChiffre = Mid(strRecherche, i, 1)
            'puis quittez la fonction
            Exit Function
        End If
    Next
   TrouverChiffre = 0
End Function

La fonction ci-dessus parcourt en boucle la chaîne fournie jusqu’à ce qu’elle trouve un nombre, puis renvoie ce nombre à partir de la chaîne. Elle ne trouvera que le premier nombre de la chaîne, car elle quittera alors la fonction.

La fonction ci-dessus peut être appelée par une sous-procédure telle que celle ci-dessous.

Sub RechercherUnNuméro()
    Dim Numéro As Integer
    'Passez une chaîne de texte à la fonction de recherche de numéro
    Numéro = TrouverChiffre("Étage supérieur, 8 Oak Lane, Texas")
    'Affichez le résultat dans la fenêtre d'exécution
    Debug.Print Numéro
End Sub

Utilisation d’une Fonction à Partir d’une Feuille Excel

En plus d’appeler une fonction à partir de votre code VBA en utilisant une sous-procédure, vous pouvez également appeler la fonction à partir de votre feuille Excel. Les fonctions que vous avez créées apparaissent par défaut dans votre liste de fonctions, dans la section « Personnalisées » de la liste des catégories.

Cliquez sur le bouton fx pour afficher la boîte de dialogue Insérer une fonction.

Sélectionnez Personnalisées dans la liste des catégories

Sélectionnez la fonction dont vous avez besoin parmi les fonctions Personnalisées disponibles.

Sinon, lorsque vous commencez à écrire votre fonction dans Excel, la fonction devrait apparaître dans la liste déroulante des fonctions.

Si vous ne voulez pas que la fonction soit disponible dans une feuille Excel, vous devez mettre le mot Private devant le mot Function lorsque vous créez la fonction dans votre code VBA.

Private Function CalculerÉcartJours(Date1 As Date, Optional Date2 As Date = "2020-06-02") As Double
    ' Calcule l'écart en jours entre les deux dates
    CalculerÉcartJours = Date2 - Date1
End Function

La fonction n’apparaîtra plus dans la liste déroulante des fonctions Excel disponibles.

Il est toutefois intéressant de noter que vous pouvez toujours utiliser la fonction – elle n’apparaîtra simplement pas dans la liste lorsque vous la rechercherez!

Si vous avez déclaré le deuxième argument comme facultatif, vous pouvez l’omettre dans la feuille Excel ainsi que dans le code VBA.

Vous pouvez également utiliser la fonction que vous avez créée sans argument dans votre feuille Excel.