Fonction VBA Excel pour retourner une plage de cellules

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Fonction pour retourner une plage

A l'instar des formules matricielles, nous allons découvrir l'astuce permettant à une fonction VBA Excel de répondre non pas par une unique valeur, mais par une plage de valeurs.



Sur l'exemple illustré par la capture, nous travaillons sur un tableau relatant les chiffres d'affaires réalisés chaque semaine et ce, une ligne sur deux. Dans la première cellule d'un tableau sur la droite, nous exploitons une fonction VBA Excel que nous avons nommée reunir. En paramètre, nous lui passons la plage représentant l'intégralité du tableau. A validation, toutes les cases sont remplies pour regrouper les chiffres qui étaient dispersés une ligne sur deux.

Ce volet est un prétexte pas totalement abouti pour apprendre à faire raisonner les fonctions VBA Excel de façon matricielle. C'est ainsi que nous exploiterons ces acquis lors d'astuces à suivre pour réaliser des extractions multiples avec une seule formule.

Classeur Excel à télécharger
Pour apprendre à créer des fonctions VBA Excel retournant des tableaux de valeurs, nous suggérons d'appuyer l'étude sur ce classeur dédié des chiffres d'affaires éparpillés.

• Télécharger le classeur fonction-vba-plage.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous retrouvons bien le tableau des chiffres d'affaires alternés avec les cumuls intercalés. Sur la droite et à partir de la cellule F4, nous attendons la réunification de ces chiffres, les uns en-dessous des autres, à l'aide d'une seule formule qu'il ne sera pas nécessaire de répliquer avec la poignée.

Créer la fonction de plage
Pour créer une fonction retournant une plage de valeurs, nous ne devons pas la typer comme un objet Range. Nous devons créer une fonction répondant par un tableau de valeurs. Pour qu'elle prenne son type au moment opportun, nous allons la typer comme un objet générique, soit comme un Variant.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• En haut de l'éditeur, déployer le menu Insertion,
• Dans les propositions, choisir l'option Module,

Nous ajoutons ainsi un nouveau module au projet. Sa feuille de code, vierge pour l'instant, apparaît au centre de l'écran.

• Dans cette feuille de code, créer la fonction reunir comme suit :

Function reunir(Tableau As Range) As Variant

End Function

Nous la déclarons fort logiquement avec un paramètre en attente. Il est typé comme un Range. Il se nomme tableau et doit représenter le tableau à réorganiser, désigné à la souris par l'utilisateur au moment de la construction de la formule.

La déclaration des variables
Désormais, nous avons besoin de certaines variables. L'une d'entre elles doit notamment représenter la plage de cellules transmise pour la parcourir une ligne sur deux. Une autre, doit représenter un tableau destiné à stocker les lignes à réunir à l'extraction.

• Dans les bornes de la fonction, ajouter les déclarations suivantes :

...
Dim leTableau As Range: Dim plage As Variant
Dim nbLignes As Byte: Dim compteur As Byte: Dim i As Byte
...

Nous déclarons la variable leTableau comme un Range pour manipuler le tableau transmis en paramètre. La variable plage est le tableau voué à réceptionner les lignes à réunir. Un objet de type Range a la capacité de prendre son type au moment de l'affectation à laquelle nous procèderons bientôt. Les trois variables suivantes sont typées comme des entiers courts (Byte). La première (nbLignes) doit mémoriser le nombre de lignes contenues dans le tableau. C'est ainsi que nous pourrons les parcourir toutes. La deuxième (compteur) sera justement utilisée comme variable de boucle. La dernière (i) doit suivre l'évolution des lignes concordantes pour les insérer dans le tableau sur les bons emplacements.

Les affectations des variables
Avant de penser à parcourir les lignes du tableau passé en paramètre de la fonction, nous devons tout d'abord initialiser les variables. Il faut bien sûr que la variable leTableau prenne possession de cette plage. De plus, le tableau de variables (plage) doit être strictement dimensionné sur le nombre d'éléments à recevoir. Sur trois colonnes, il s'agit de réceptionner les informations de la moitié des lignes (une ligne sur deux).

• A la suite du code VBA, ajouter les affectations suivantes :

...
Set leTableau = Tableau: i = 1
nbLignes = leTableau.Rows.Count
ReDim plage(1 To Int(nbLignes / 2), 1 To 3)
...

Nous initialisons (Set) l'objet leTableau sur la plage transmise. Nous réglons la variable i sur la position 1. En effet, la première insertion doit avoir lieu dans la première ligne du tableau de variables. Nous l'incrémenterons au fur et à mesure de la progression de la boucle. Ensuite, grâce à la propriété Count de la collection Rows (Lignes) de l'objet leTableau fraîchement affecté, nous récoltons le nombre de lignes de la plage, que nous stockons dans la variable nbLignes. Nous utilisons cette dernière dans l'enchaînement avec l'instruction Redim. Cette instruction permet de définir les dimensions du tableau de variables. Nous lui affectons trois colonnes (1 To 3) pour recevoir les trois informations du tableau d'origine. Puis, comme nous n'allons prélever qu'une ligne sur deux, nous définissons son nombre de lignes sur la moitié de celles contenues dans la plage transmise à la fonction (1 To Int(nbLignes / 2)). La fonction Int permet de forcer la conversion en entier si d'aventure le nombre de lignes n'était pas un nombre pair.

Parcourir les lignes du tableau
Nous devons maintenant parcourir chaque ligne du tableau transmis en paramètre. Pour cela, nous allons exploiter une boucle For Next classique. Nous devons débuter l'analyse à partir de la première ligne du tableau et la poursuivre jusqu'à la dernière.

• A la suite du code VBA, créer la boucle suivante :

...
For compteur = leTableau.Row To leTableau.Row + nbLignes - 1

Next compteur
...

C'est la propriété Row d'un objet de type Range qui renseigne sur la première ligne de la plage ainsi désignée. Comme cette première ligne est d'ores et déjà incluse dans l'analyse, nous retranchons une unité au nombre total de lignes que nous lui ajoutons (leTableau.Row + nbLignes - 1), pour rejoindre (To) la dernière ligne du tableau.

Prélever les informations une ligne sur deux
Désormais et nous l'avons dit, pour réunir les informations, à chaque passage dans cette boucle, nous devons prélever les données une ligne sur deux. Pour cela, nous allons exercer l'opérateur Mod sur la variable compteur qui rend compte de la progression. Cet opérateur permet de connaître le reste d'une division. Si le reste de la division de la variable compteur par 2 vaut 0, nous saurons qu'il s'agit d'une ligne paire à intégrer dans le tableau de variables.

• Dans les bornes de la boucle, ajouter l'instruction conditionnelle suivante :

...
If (compteur Mod 2 = 0) Then
plage(i, 1) = Range("B" & compteur).Value
plage(i, 2) = Range("C" & compteur).Value
plage(i, 3) = Range("D" & compteur).Value
i = i + 1
End If
...

Si la condition est vérifiée, dans la rangée en cours (i) que nous n'oublions pas d'incrémenter à chaque passage (i = i + 1), nous stockons les informations de la ligne en cours pour les trois colonnes (1:B, 2:C, 3:D). Dans l'objet Range qui désigne une plage de cellules ou une cellule à défaut comme ici, Il est intéressant de constater que nous pouvons concaténer l'indice dynamique de ligne (& compteur) avec l'indice statique colonne (B, C ou D).

Retourner la plage de valeurs
A l'issue du traitement de la boucle, le tableau de variables est susceptible d'être rempli de toutes les données réunifiées. Comme vous le savez, une fonction VBA Excel retourne un résultat par son propre nom. Et une fois n'est pas coutume, pour qu'elle retourne une plage de valeurs, nous allons l'affecter sur le tableau de variables. Voilà donc l'astuce pour qu'une fonction VBA Excel livre des résultats semblables à ceux d'une formule matricielle.

• Après la boucle, ajouter l'affectation suivante :

...
reunir = plage
...

Il ne nous reste plus qu'à tester cette fonction en conditions réelles.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Sélectionner la case du premier résultat à extraire en cliquant sur sa cellule F4,
• Taper le symbole égal (=) pour initier la syntaxe de la formule,
• Inscrire le nom de la fonction que nous venons de créer, suivie d'une parenthèse, soit : reunir(,
• Désigner l'intégralité du tableau à traiter en sélectionnant la plage de cellules B4:D17,
• Fermer la parenthèse de la fonction reunir,
• Enfin, valider la formule en toute simplicité avec la touche Entrée du clavier,

Comme vous pouvez l'apprécier, ses résultats se répandent automatiquement sur les colonnes et les lignes adjacentes du fait du dimensionnement du tableau de variables retourné par la fonction.



Nous l'avons dit, ce volet était un prétexte pour comprendre le mécanisme de ces fonctions particulières, capables de répondre par des tableaux de valeurs. Nous exploiterons ces acquis à des fins plus utiles dans une prochaine astuce. Il sera question d'extraire toutes les données d'une base, attachées à une référence désignée.

Le code complet de la fonction VBA Excel que nous avons construite, est le suivant :

Function reunir(Tableau As Range) As Variant
Dim leTableau As Range: Dim plage As Variant
Dim nbLignes As Byte: Dim compteur As Byte: Dim i As Byte

Set leTableau = Tableau: i = 1
nbLignes = leTableau.Rows.Count
ReDim plage(1 To Int(nbLignes / 2), 1 To 3)

For compteur = leTableau.Row To leTableau.Row + nbLignes - 1
If (compteur Mod 2 = 0) Then
plage(i, 1) = Range("B" & compteur).Value
plage(i, 2) = Range("C" & compteur).Value
plage(i, 3) = Range("D" & compteur).Value
i = i + 1
End If
Next compteur

reunir = plage

End Function