Comment utiliser le tableau de paramètres (ParamArray) pour un nombre d’arguments variable en VBA Excel

Pour ce nouveau chapitre de la formation dédiée à l’apprentissage de VBA sur Excel, je vais vous présenter le fonctionnement du tableau de paramètres (ParamArray) qui permet de faire passer plusieurs paramètres en tant que tableau (Array). De cette manière, le nombre de paramètres nécessaire au fonctionnement d’une méthode (ou d’une fonction) n’a pas besoin d’être défini.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Qu’est-ce qu’un tableau de paramètres

Dans ce tutoriel, nous allons voir comment créer une fonction dans laquelle nous allons souhaiter faire passer un tableau en tant qu’argument, en utilisant ce que nous appellerons un tableau de paramètres.

Nous verrons que cela nous permettra en autres choses de pouvoir passer un nombre non défini d’arguments.

L’une des fonctions intégrées à Excel utilisant ce type de paramètre est la fonction SOMME().

En effet, pour utiliser cette fonction, nous pouvons saisir différents types d’arguments, quel qu’en soit le nombre :

• Nous pouvons saisir une valeur unique :

=SOMME(120)

• Plusieurs valeurs

=SOMME(120;10;-5)

• Des cellules :

=SOMME(B21;C21;D21)

• Une plage de cellules :
• :

=SOMME(B21:D21)

• Plusieurs plages de cellules :

=SOMME(B21:C21;D21)

• Mais également des mélanges de plusieurs types :

=SOMME(120;B21:C21;D21)

Pour arriver à réaliser cela, l’argument est ici déclaré en utilisant le mot-clé ParamArray (tableau de paramètres), que nous pourrons identifier lors de l’utilisation des fonctions avec les crochets

Bien entendu, pour comprendre l’usage du tableau de paramètres, il est nécessaire de comprendre comment fonctionne une variable de type Tableau, en consultant le chapitre dédié de la formation sur l’apprentissage de VBA, dont le fonctionnement est identique.

Pour résumer, les tableaux de variables permettent de manipuler des ensembles de variables d’un même type, en les rassemblant dans une seule et même variable identifiée par un index.

2. Créer la fonction SOMME_INTERVALLE()

Pour illustrer l’usage que nous pouvons avoir du tableau de paramètres, nous allons reproduire la fonction SOMME(), que nous allons personnaliser pour ne prendre en compte que les valeurs comprises dans un intervalle, c’est-à-dire entre une valeur plancher, et une valeur plafond.

Pour cela, nous allons partir du tableau suivant :

Dans celui-ci, nous retrouvons les ventes réalisées par une entreprise, mois par mois, dans trois pays (la France, L’Allemagne et l’Espagne).

Nous allons voir regrouper les niveaux d’activités mensuels en fonction des paliers de réalisation du Chiffre d’affaires :

• De zéro à 300 000€
• De 301 000 à 600 000€
• Et de 601 000€ à 1 000 000€

Bien entendu, pour créer cette fonction, nous allons utiliser VBA :

• Pour commencer, nous lancer VBE en appuyant sur les touches [Alt]+[F11],
• Ensuite, nous créons un nouveau module : Menu Insertion > Module :

Et c’est dans ce module que nous allons insérer la fonction, en saisissant le mot-clé Function, suivi du nom de la fonction, par exemple SOMME_INTERVALLE() :

Function SOMME_INTERVALLE()
 
End Function

Lorsque que nous validons la création de la fonction en appuyant sur la touche [Entrée] du clavier, Excel insère automatiquement la ligne End Function.

Tout ce que nous saisirons entre la ligne commençant par Function, et celle terminant par Function sera exécuté à chaque fois que nous appellerons la fonction.

Dans un premier temps, pour comprendre le fonctionnement du tableau de paramètres, nous allons nous contenter de dupliquer le fonctionnement de la fonction SOMME() classique.

Comme nous l’avons vu, la déclaration de l’argument passe par l’utilisation du mot-clé ParamArray :

Function SOMME_INTERVALLE(ParamArray  valeurs() As Variant)
 
End Function

Attention, un argument défini avec le mot-clé ParamArray, doit obligatoirement être typé en tant que Variant.

À partir de ce moment-là, il est donc possible d’exploiter un nombre non défini de variable directement à partir de valeurs.

Pour en récupérer la somme, il suffit donc d’utiliser une boucle For… Each :

Function SOMME_INTERVALLE(ParamArray  valeurs() As Variant)
    Dim total As Double
    Dim v As Variant
    
    For Each v In valeurs
        total = total + v
    Next
    
    SOMME_INTERVALLE = total
End Function

Nous commençons ici par déclarer deux variables :

• Une variable v qui va permettre de passer en revue tous les éléments de l’argument passé en tant que ParamArray,
• Une seconde variable total qui va permette de récupérer la valeur totale de chacun de ces éléments. Cette variable est typée en tant que Double, c’est-à-dire un grand nombre décimal.

Ensuite, nous mettons en place une boucle For… Each dans laquelle nous allons affecter à la variable v le contenu de chacun des éléments de ParamArray.

Et pour finir, pour sommer les éléments, nous affectons à la variable total sa propre valeur à laquelle nous ajoutons la valeur de chaque variable v

Nous pouvons maintenant tester en revenant dans la feuille de calcul, puis en appelant la fonction que nous venons de créer, comme nous le ferions pour n’importe quelle fonction d’Excel :

Pour le moment, nous ne pouvons calculer que des valeurs numériques, mais peu importe le nombre d’argument !

En effet, si nous essayons d’y insérer une référence de cellule, nous aurons alors une erreur :

Pour contourner cette erreur, nous commençons par regarder si l’argument en cours d’analyse (v) est une valeur numérique, et lorsque c’est le cas, nous venons y intercaler le calcul de somme que nous venons de voir :

        If IsNumeric(v) Then
            total = total + v
        Else
        End If

Dans le cas contraire, nous considèrerons qu’il s’agira d’une plage de cellules.

Et à ce moment-là, pour en effectuer la somme, nous allons déclarer deux variables :

• La variable plage qui va correspondre à v, ce dernier ne pouvant pas être exploité directement,
• La variable cellule qui va permettre de passer en revue toutes les cellules de la plage actuelle

Puis nous allons utiliser une nouvelle boucle pour passer en revue toutes les cellules de la plage :

            Dim plage As Range, cellule As Range
            Set plage = v
            
            For Each cellule In plage
                total = total + cellule
            Next

Et voilà, nous pouvons maintenant tester la fonction sur la feuille de calcul, en utilisant, des valeurs numériques, ou encore des plages de cellules complexes :

(Code)=SOMME_INTERVALLE(454872;B10;B11:B20)(/Code)

Maintenant, pour pouvoir ajouter les valeurs d’intervalle à notre fonction, nous allons devoir introduire deux nouveaux arguments valeurMin et valeurMax.

La seule contrainte étant que nous devons conserver le ParamArray en toute fin de liste d’argument !

Ces nouveaux arguments devront donc être insérés juste avant :

Function SOMME_INTERVALLE(valeurMin As Double,  valeurMax As Double, ParamArray valeurs() As Variant) 

Maintenant que ces arguments ont été créés, nous pouvons les utiliser pour savoir si la valeur doit être sommée :

…
            If v >= valeurMin And v  <= valeurMax Then total = total + v
…                If cellule >= valeurMin And  cellule <= valeurMax Then total = total + cellule

=SOMME_INTERVALLE(0;300000;B9:B20)

=SOMME_INTERVALLE(300001;600000;B9:B20)

=SOMME_INTERVALLE(600001;1000000;B9:B20)

Et voilà, nos trois tranches sont maintenant correctement calculées !