Tout sur les procédures Function en VBA [#07 FORMATION EXCEL VBA COMPLETE]

Dans ce nouveau chapitre de la formation VBA d’Excel, nous allons découvrir un nouveau type de procédure : il s’agit du type function, qui permet de retourner un résultat !

Téléchargement

Vous pouvez obtenir le fichier d'exemple de cet article et découvrir la méthode d'apprentissage d'Excel pas-à-pas en cliquant sur le lien suivant :

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Qu’est-ce qu’une procédure

Dans le chapitre précédent de la formation VBA, nous avions détaillé les procédures de type Sub qui permettent de réaliser des suites d’instructions, et qui constituent le cœur des macros commandes VBA. Nous avions également abordé l’existence d’un second type de procédure qui permet de retourner un résultat : il s’agit des procédures de type Function que nous allons à présent découvrir dans ce chapitre.

Pour faciliter la compréhension, nous utiliserons plutôt le terme de « fonction » pour désigner une procédure de type Function.

Pour bien comprendre la notion de retour, nous pouvons simplement faire l’analogie avec quelque chose qui nous est déjà très familier : il s’agit des fonctions EXCEL que nous utilisons depuis toujours dans nos feuilles de calculs !

En effet, prenons l’exemple de la fonction suivante :

 =SOMME(3;4) 

Lorsque nous saisissons cette fonction, nous spécifions les valeurs de deux paramètres, et nous attendons d’Excel le résultat de l’addition de ces valeurs.

Une fonction VBA fonctionne exactement de la même manière :

• Nous renseignons des paramètres à la fonction, peu importe leur nombre, il existe même des fonctions sans paramètre,
• Puis nous récupérons un résultat

Voici par exemple une fonction VBA qui va se charger d’effectuer le même type d’opération :

 Function maSomme(a, b)
    maSomme = a + b
End Function 

Nous pouvons noter deux choses :

• Le mot clé « Function » remplace le mot clé « Sub » que nous avons déjà vu dans le chapitre précédent,
• Pour affecter une valeur à une fonction, nous utilisons simplement l’opérateur d’égalité (le signe égal)

Par contre, il est évidemment impossible de lancer une fonction avec des arguments depuis la fenêtre de lancement de macro. En effet cette option de permet pas de spécifier de paramètre directement.

Le plus simple pour tester ce bout de code, c’est de créer une procédure de type Sub qui va se charger d’executer directement la fonction :

 Sub testerFonction()
    Dim maVariable As Double
    maVariable = maSomme(1.5, 3.6)
    MsgBox maVariable
End Sub 

Et voici le résultat ainsi affiché :

Plutôt simple, non ?

2. Typage de fonction et de paramètre

Ensuite, sachez que comme pour les variables, il est tout à fait possible de type une fonction afin de garder le contrôle sur le résultat retourné :

 Function maSomme(a, b) As Double
    maSomme = a + b
End Function 

Ici la fonction retournera un résultat numérique à virgule. Sous peine de générer une erreur d’execution !

Les variables saisies en paramètres peuvent elles aussi être typées :

 Function maSomme(a As Double, b As Double) As
 Double
    maSomme = a + b
End Function 

Cela évitera par exemple de saisir une chaîne de texte en guise d’argument !

3. Les arguments optionnels

Lorsque l’on souhaite affecter une valeur par défaut à une variable, il est possible de rendre la saisie d’un paramètre optionnelle, grâce à l’instruction Optional.

Pour vérifier la présence ou non de l’argument, nous utiliserons par exemple l’instruction IsMissing() qui permet de savoir si un paramètre optionel de type Variant est présent :

 Function maSomme(a As Double, Optional b
 As Variant) As Double
    If IsMissing(b) Then b = 5
    maSomme = a + b
End Function
 
Sub testerFonction()
    Dim maVariable As Double
    maVariable = maSomme(1.5)
    MsgBox maVariable
End Sub 

Vous noterez au passage le changement de type de b qui devient ici une variable de type Variant !

Il est également possible d’affecter une valeur par défaut directement au niveau de la saisie des arguments :

 Function maSomme(a As Double, Optional b
 As Double = 5) As Double
    maSomme = a + b
End Function 

Cette méthode est encore plus simple à utiliser que celle vue juste avant.

4. Utiliser un nombre indéfini de paramètres

Enfin, lorsque nous ne connaissons pas le nombre d’arguments saisis par l’utilisateur, il est possible d’utiliser le typage ParamArray. Cela va être particulièrement utile dans notre fonction de somme, afin de ne pas limiter l’usage à seulement deux variables à additonner comme nous venons de le faire dans l’exemple au dessus :

 Function maSomme(ParamArray nombres() As
 Variant) As Double
    Dim resultat As Double
    For Each nombre In nombres
        resultat = resultat + nombre
    Next
    maSomme = resultat
End Function
 
Sub testerFonction()
    Dim maVariable As Double
    maVariable = maSomme(1.5, 2.5, -7, 10.6)
    MsgBox maVariable
End Sub 

Nous abordons ici rapidement la notion de ParamArray, nous y reviendrons plus tard dans le détail, sachez seulement qu’un seul paramètre peut être déclaré comme ParamArray, que celui-ci doit absolument être le tout dernier des paramètres, et qu’il doit être de type Variant.

5. Utiliser les fonction VBA dans les formules Excel

Pour finir, sachez qu’un énorme avantage des procédures Function, c’est que celles-ci peut être directement utilisées dans une feuille de calcul !

Pour tester, il suffit de saisir simplement le signe égal puis le nom de la fonction dans une cellule :